PostgreSQL CREATE TABLE

 Summary: in this tutorial, you will learn how to use the PostgreSQL CREATE TABLE statement to create new tables.

PostgreSQL CREATE TABLE syntax

To create a new table in PostgreSQL, you use the CREATE TABLE statement. The following illustrates the syntax of the CREATE TABLE statement:

Let’s examine the syntax of the CREATE TABLE statement  in more detail.

• First, you specify the name of the new table after the CREATE TABLE clause. The TEMPORARY keyword is for creating a temporary table, which we will discuss in the temporary table tutorial.
• Next, you list the column name, its data type, and column constraint. You can have multiple columns in a table, each column is separated by a comma (,). The column constraint defines the rules for the column e.g.,  NOT NULL.
• Then, after the column list, you define a table-level constraint that defines rules for the data in the table.
• After that, you specify an existing table from which the new table inherits. It means the new table contains all columns of the existing table and the columns defined in the CREATE TABLE statement. This is a PostgreSQL’s extension to SQL.

PostgreSQL column constraints

The following are the commonly used column constraints in PostgreSQL:

• NOT NULL – the value of the column cannot be NULL.
• UNIQUE – the value of the column must be unique across the whole table. However, the column can have many NULL values because PostgreSQL treats each NULL value to be unique. Notice that SQL standard only allows one NULL value in the column that has the UNIQUE constraint.
• PRIMARY KEY – this constraint is the combination of NOT NULL and UNIQUE constraints. You can define one column as PRIMARY KEY by using column-level constraint. In case the primary key contains multiple columns, you must use the table-level constraint.
• CHECK – enables to check a condition when you insert or update data. For example, the values in the price column of the product table must be positive values.
• REFERENCES – constrains the value of the column that exists in a column in another table. You use REFERENCES to define the foreign key constraint.

PostgreSQL table constraints

The table constraints are similar to column constraints except that they are applied to the entire table rather than to an individual column.

The following are the table constraints:

• UNIQUE (column_list)–  to force the value stored in the columns listed inside the parentheses to be unique.
• PRIMARY KEY(column_list) – to define the primary key that consists of multiple columns.
• CHECK (condition) – to check a condition when inserting or updating data.
• REFERENCES– to constrain the value stored in the column that must exist in a column in another table.

PostgreSQL CREATE TABLE example

We will create a new table named account that has the following columns with the corresponding constraints:

• user_id – primary key
• username – unique and not null
• password – not null
• email – unique and not null
• created_on – not null
• last_login – null

The following statement creates the accounttable:

The following statement creates  role table that consists of two columns: role_id and role_name:

The following statement creates the account_roles table that has three columns: user_id, role_id and grant_date.

Let’s examine the above statement in more detail.

The primary key of the account_role table consists of two columns: user_id and role_id, therefore we have to use primary key table-level constraint to define the primary key as follows:

Because the user_idcolumn references to the user_idcolumn in the account table, we need to define a foreign key constraint for the user_idcolumn:

The role_idcolumn references to the role_idcolumn in the role table, we also need to define a foreign key constraint for the role_idcolumn.

In this tutorial, we have shown you how to use the PostgreSQL CREATE TABLE statement to create new tables. We also showed you how to apply different kinds of constraints at column and table level.