PostgreSQL CREATE TABLE Syntax

A relational database consists of multiple related tables. A table consists of rows and columns. Tables allow you to store structured data like customers, products, employees, etc.

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

CREATE TABLE [IF NOT EXISTS] table_name (
   column1 datatype(length) column_contraint,
   column2 datatype(length) column_contraint,
   column3 datatype(length) column_contraint,
   table_constraints
);
Code language: SQL (Structured Query Language) (sql)

PostgreSQL Column Constraints

PostgreSQL includes the following column constraints:

NOT NULL – ensures that values in a column cannot be NULL.
UNIQUE – ensures the values in a column unique across the rows within the same table.
PRIMARY KEY – a primary key column uniquely identify rows in a table. A table can have one and only one primary key. The primary key constraint allows you to define the primary key of a table.
CHECK – a CHECK constraint ensures the data must satisfy a boolean expression.
FOREIGN KEY – ensures values in a column or a group of columns from a table exists in a column or group of columns in another table. Unlike the primary key, a table can have many foreign keys.

Table constraints are similar to column constraints except that they are applied to more than one column.

PostgreSQL CREATE TABLE Examples

We will create a new table called accounts that has the following columns:

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 accounts table:

CREATE TABLE accounts (
  user_id serial PRIMARY KEY,
  username VARCHAR ( 50 ) UNIQUE NOT NULL,
  password VARCHAR ( 50 ) NOT NULL,
  email VARCHAR ( 255 ) UNIQUE NOT NULL,
  created_on TIMESTAMP NOT NULL,
  last_login TIMESTAMP 
);
Code language: SQL (Structured Query Language) (sql)

PostgreSQL Create Table - accounts example

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

CREATE TABLE roles(
  role_id serial PRIMARY KEY,
  role_name VARCHAR (255) UNIQUE NOT NULL
);
Code language: SQL (Structured Query Language) (sql)

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

CREATE TABLE account_roles (
  user_id INT NOT NULL,
  role_id INT NOT NULL,
  grant_date TIMESTAMP,
  PRIMARY KEY (user_id, role_id),
  FOREIGN KEY (role_id)
  REFERENCES roles (role_id),
  FOREIGN KEY (user_id)
  REFERENCES accounts (user_id)
);
Code language: SQL (Structured Query Language) (sql)

PostgreSQL Create Table - account_roles example

The primary key of the account_roles table consists of two columns: user_id and role_id, therefore, we have to define the primary key constraint as a table constraint.

PRIMARY KEY (user_id, role_id)
Code language: SQL (Structured Query Language) (sql)

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

FOREIGN KEY (user_id)
REFERENCES accounts (user_id)
Code language: SQL (Structured Query Language) (sql)

The role_id column references the role_id column in the roles table, we also need to define a foreign key constraint for the role_id column.

FOREIGN KEY (role_id)
REFERENCES roles (role_id)
Code language: SQL (Structured Query Language) (sql)

The following shows the relationship between the accounts, roles, and account_roles tables:

Summary

Use the CREATE TABLE statement to create a new table.
Use the IF NOT EXISTS option to create the new table only if it does not exist.
Apply the primary key, foreign key, not null, unique, and check constraints to columns of a table.

All the PostgreSQL posts:

PostgreSQL – Create Table

How to create your first VPS on DigitalOcean