PostgreSQL – CREATE TABLE

In PostgreSQL, the CREATE TABLE clause as the name suggests is used to create new tables.

Syntax:
CREATE TABLE table_name (
    column_name TYPE column_constraint,
    table_constraint table_constraint
) INHERITS existing_table_name;

Let’s analyze the syntax above:

• First, you define the name of the new table after the CREATE TABLE clause. The TEMPORARY keyword is for creating a temporary table.
• Next, one has to list the column name, its data type, and column constraint. A table may have multiple columns separated by a comma (, ). The column constraint defines rules for the column e.g., NOT NULL.
• Then, after the column list, one has to define a table-level constraint that defines rules for the data in the table.
• After that, one sets 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.

Now let’s jump into an example.

Example:
In this 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 account table:

CREATE TABLE account(
    user_id serial PRIMARY KEY,
    username VARCHAR (50) UNIQUE NOT NULL,
    password VARCHAR (50) NOT NULL,
    email VARCHAR (355) UNIQUE NOT NULL,
    created_on TIMESTAMP NOT NULL,
    last_login TIMESTAMP
);

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

CREATE TABLE role(
    role_id serial PRIMARY KEY,
    role_name VARCHAR (255) UNIQUE NOT NULL
);

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

CREATE TABLE account_role
(
  user_id integer NOT NULL,
  role_id integer NOT NULL,
  grant_date timestamp without time zone,
  PRIMARY KEY (user_id, role_id),
  CONSTRAINT account_role_role_id_fkey FOREIGN KEY (role_id)
      REFERENCES role (role_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT account_role_user_id_fkey FOREIGN KEY (user_id)
      REFERENCES account (user_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);

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 the primary key table-level constraint to define the primary key as follows:

PRIMARY KEY (user_id, role_id)

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

CONSTRAINT account_role_user_id_fkey FOREIGN KEY (user_id)
      REFERENCES account (user_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION

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

CONSTRAINT account_role_role_id_fkey FOREIGN KEY (role_id)
      REFERENCES role (role_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION, 

Output: