Open In App

PostgreSQL – CREATE TABLE

Last Updated : 28 Aug, 2020
Improve
Improve
Like Article
Like
Save
Share
Report

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:


Previous Article
Next Article

Similar Reads

PostgreSQL - Export PostgreSQL Table to CSV file
In this article we will discuss the process of exporting a PostgreSQL Table to a CSV file. Here we will see how to export on the server and also on the client machine. For Server-Side Export: Use the below syntax to copy a PostgreSQL table from the server itself: Syntax: COPY Table_Name TO 'Path/filename.csv' CSV HEADER; Note: If you have permissio
2 min read
PostgreSQL - Connect To PostgreSQL Database Server in Python
The psycopg database adapter is used to connect with PostgreSQL database server through python. Installing psycopg: First, use the following command line from the terminal: pip install psycopg If you have downloaded the source package into your computer, you can use the setup.py as follows: python setup.py build sudo python setup.py installCreate a
4 min read
PostgreSQL - Installing PostgreSQL Without Admin Rights on Windows
If you are a part of a corporation, it is highly unlikely that you have the admin privileges to install any external software. But the curious souls that all software developers are, in this article, we will see the detailed process of installation of PostgreSQL without having administrator rights on our Windows machine. Installation: Follow the be
4 min read
PostgreSQL - CREATE TABLE AS
In PostgreSQL, the CREATE TABLE AS statement is used to create a new table and fill it with the data returned by a query. Syntax: CREATE TABLE new_table_name AS query; Let's analyze the above syntax: First, specify the new table name after the CREATE TABLE clause. Finally, provide a query whose result set is added to the new table after the AS keyw
2 min read
PostgreSQL - Create table using Python
This article explores the process of creating table in The PostgreSQL database using Python. Prerequisites:psycopg2 modulesample databaseCreating a Table: To create a table in the database use the following steps: First create a CREATE TABLE statementSecond establish a connection to the database using the connect() functionThird construct a cursor
2 min read
PostgreSQL - Size of a Table
In this article, we will look into the function that is used to get the size of the PostgreSQL database table. In this article, we will be using a sample database for reference which is described here and can be downloaded from here. The pg_relation_size() function is used to get the size of a table. Syntax: select pg_relation_size('table_name'); E
2 min read
PostgreSQL - Import CSV File Into Table
In this article, we will discuss the process of importing a .csv file into a PostgreSQL table. To do so we will require a table which can be obtained using the below command: CREATE TABLE persons ( id serial NOT NULL, first_name character varying(50), last_name character varying(50), dob date, email character varying(255), CONSTRAINT persons_pkey P
2 min read
PostgreSQL - Temporary Table
A temporary table, as the name implies, is a short-lived table that exists for the duration of a database session. PostgreSQL automatically drops the temporary tables at the end of a session or a transaction. Syntax: CREATE TEMPORARY TABLE temp_table( ... ); or, CREATE TEMP TABLE temp_table( ... ); A temporary table is visible only to the session t
1 min read
PostgreSQL - Temporary table name
In PostgreSQL, A temporary table can have the same name as of a permanent table, even though it is not recommended. When the user creates a temporary table with the same name as a permanent table, it cannot access the permanent table until the temporary table is removed. Example: First, create a table named customers: CREATE TABLE customers(id SERI
2 min read
PostgreSQL - Removing Temporary Table
In PostgreSQL, one can drop a temporary table by the use of the DROP TABLE statement. Syntax: DROP TABLE temp_table_name; Unlike the CREATE TABLE statement, the DROP TABLE statement does not have the TEMP or TEMPORARY keyword created specifically for temporary tables. To demonstrate the process of dropping a temporary table let's first create one b
2 min read