PostgreSQL – CREATE TABLE

In PostgreSQL, the CREATE TABLE clause as the name suggests is used to create a new table. Creating tables is a fundamental aspect of database design in PostgreSQL.

Let us get a better understanding of the CREATE TABLE Clause in PostgreSQL.

Syntax

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

Let’s analyze the syntax above:

• Table Name: Define the name of the new table after the CREATE TABLE clause. Use the TEMPORARY keyword if you’re creating a temporary table.
• Column Definition: List the column name, data type, and constraint. Columns are separated by a comma (,). Column constraints include rules like NOT NULL.
• Table-Level Constraints: Define rules for the data in the table at a broader level, such as primary and foreign keys.
• Inheritance: Specify an existing table from which the new table inherits columns. This is a PostgreSQL extension to SQL, making table creation more flexible.

PostgreSQL CREATE TABLE Example

Now let us take a look at an example of the CREATE TABLE in PostgreSQL to better understand the concept.

1. Creating the ‘account' Table

In this example, we create a new table named account with the following columns and 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
);

2. Creating the ‘role' Table

Next, we create the role table with two columns: ‘role_id' and ‘role_name'.

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

3. Creating the account_role Table

Finally, we create the ‘account_role' table to manage the relationship between users and roles. This table 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
);

Detailed Analysis

Primary Key Constraint

The primary key for the account_role table consists of two columns: ‘user_id' and ‘role_id'. The primary key constraint ensures that each combination of ‘user_id' and ‘role_id' is unique.

PRIMARY KEY (user_id, role_id)

Foreign Key Constraints

Foreign key constraints ensure referential integrity between tables. The ‘user_id' in ‘account_role' references the ‘user_id' in the ‘account' table, while ‘role_id' references the ‘role_id' in the role table.

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_id’ column 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:

Important Points About CREATE TABLE Clause in PostgreSQL

• The CREATE TABLE clause is used to define a new table in the database.
• Use the ‘INHERITS' clause to create a table that inherits columns from an existing table.
• Use the ‘TEMPORARY‘ or ‘TEMP' keyword to create tables that exist only for the duration of the session.
• Specify a tablespace for storing the table using the ‘TABLESPACE' clause.
• Use the PARTITION BY clause to define table partitioning, which helps manage large tables.
• Tables can be created within specific schemas for better organization.

R

RajuKumar19

Follow

Improve