--description--

To define a table in SQL, you need to specify the data type of each column.

Remember that this is the basic syntax for creating a table in SQL. Each column has its own data type.

CREATE TABLE table_name(
  column1 data_type column_constraint,
  column2 data_type column_constraint,
  column3 data_type column_constraint,
  ... etc
);

As a developer, your goal is to choose these data types as accurately as possible.

There are six popular categories of data types in SQL:

• Numeric, such as INTEGER, FLOAT, SERIAL, and DECIMAL.
• Date and time, such as TIMESTAMP, DATE, and TIME.
• Character and string. These include CHAR, VARCHAR, and TEXT.
• Unicode, including NTEXT, and NVARCHAR. These are used to make sure that text will be stored and retrieved correctly, regardless of the characters' origin.
• Binary, used to store non-textual data, like images, audio, and video files.
• And other miscellaneous data types, such as XML and TABLE.

In this lesson, we'll cover some of the most widely used ones with PostgreSQL. Data types will vary across database management systems, but they are generally pretty similar. So you'll need to check the full list in the documentation.

We'll start with numeric values.

First, we have the INTEGER data type:

units_sold INTEGER

In this example, we assign this data type to a units_sold column.

INTEGER types consume 4 bytes in the database and can range in value from -2,147,483,648 to 2,147,483,647. The official PostgreSQL documentation describes it as the "typical choice for integer."

We also have SMALLINT and BIGINT, which are basically the same as INTEGER except have a smaller and bigger range of numbers, respectively, due to how much size they are allotted in the database.

A useful feature available in PostgreSQL is to create a column using the SERIAL keyword. Here, an id column is created using SERIAL:

id SERIAL

While it's not a true data type, it's very helpful for creating unique identifier columns because the column will have an INTEGER type, will not allow NULL values, and automatically increment when rows are added.

For example, the first row will automatically have an id of 1, the second row will have an id of 2, and so on, creating a unique ID for each record.

In MySQL, the equivalent of SERIAL would be the AUTO_INCREMENT attribute, used to generate sequential integers automatically:

id INT AUTO_INCREMENT

You can see that different database management systems may have different ways of achieving exactly the same functionality in your database.

These are the most commonly used numeric data types, but sometimes, you may need to represent text or sequences of characters.

For this, you have the VARCHAR data type. This data type is used for a variable string length. You can set the maximum character length within parentheses:

name VARCHAR(50)

In this example, we define a name column, where the values can be up to 50 characters long.

This data type sets a maximum length for the strings, but if you need to store strings of any length, you can use TEXT instead:

name TEXT

In addition to numbers and strings, it's also common to store dates and times.

For example, if you create a table to store events, you may need to store the date of each event in a specific format. For this, you have the DATE data type:

event_date DATE

To store time, you can also use the TIME data type. For example, you may store the time when an event starts:

start_time TIME

The TIMESTAMP data type combines both of them. It includes both the date and time. It may also include the time zone if you write TIMESTAMP WITH TIME ZONE:

event_timestamp TIMESTAMP

event_timestamp TIMESTAMP WITH TIME ZONE

And finally, if you need to store a boolean value, TRUE or FALSE, you can use the BOOLEAN data type:

is_active BOOLEAN

Now you know some of the most common data types in SQL, but there are many more. You can check the documentation of your database management system to find more information about the data types that you can use.

--questions--

--text--

Which data type is used to store whole numbers in PostgreSQL?

--answers--

VARCHAR

--feedback--

Think about which option represents a numerical value without decimal points.

TEXT

--feedback--

Think about which option represents a numerical value without decimal points.

INTEGER

BOOLEAN

--feedback--

Think about which option represents a numerical value without decimal points.

--video-solution--

3

--text--

If you need to store a long text document or a very long string, which data type should you use in PostgreSQL?

--answers--

VARCHAR(255)

--feedback--

Think about which option is designed to store character strings of unlimited or very large lengths.

INTEGER

--feedback--

Think about which option is designed to store character strings of unlimited or very large lengths.

CHAR(100)

--feedback--

Think about which option is designed to store character strings of unlimited or very large lengths.

TEXT

--video-solution--

4

--text--

Which data type would you use to store a TRUE or FALSE value in a PostgreSQL database?

--answers--

INTEGER

--feedback--

Think about the data type that represents a logical state.

VARCHAR

--feedback--

Think about the data type that represents a logical state.

BOOLEAN

TEXT

--feedback--

Think about the data type that represents a logical state.

--video-solution--

3