PostgreSQL CREATE TABLE

Many times you may need to create table in PostgreSQL. Here’s how to create table using PostgreSQL CREATE TABLE command.

Table of Contents

How To Create Table in PostgreSQL

Here are the steps to create table in PostgreSQL.

PostgreSQL CREATE TABLE allows you to create new table in database. Here’s the syntax.

CREATE TABLE [IF NOT EXISTS] table_name(
   column_1_definition,
   column_2_definition,
   ...,
   table_constraints
);

In the above create table query, table_name is the name of your new table. IF NOT EXISTS is an optional argument that checks if the table already exists and creates table only if it doesn’t exist.

column_1_definition, column_2_definition, … are different column definitions each consisting of column name and data type.

table_contraints are different types of optional constraints such as foreign key constraint, primary key constraint, etc.

Bonus Read : How to Create Histogram in PostgreSQL

PostgreSQL CREATE TABLE examples

Here’s the SQL query to create table in PostgreSQL. We will create an orders table to store the list of orders

postgres=#CREATE TABLE IF NOT EXISTS orders (
    id INT PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    order_date DATE,
    price INT NOT NULL,
    description TEXT,
    created_at TIMESTAMP NOT NULL
) ;

postgres=# \d orders;
                 Table "public.orders"
    Column    |            Type             | Modifiers
--------------+-----------------------------+-----------
 id           | integer                     | not null
 product_name | character varying(255)      | not null
 order_date   | date                        |
 price        | integer                     | not null
 description  | text                        |
 created_at   | timestamp without time zone | not null

In the above query,

id is our table’s primary key.

product_name is a VARCHAR (string) column to store product name

order_date is a date column. Since it doesn’t have any constraints, it can even have NULL values.

price is an integer column with a NOT NULL constraint. So it cannot have null values.

created_at is a timestamp column and cannot have NULL values.

Bonus Read : How to Create User in PostgreSQL

PostgreSQL CREATE TABLE with FOREIGN KEY

Let’s create a new table in PostgreSQL with FOREIGN KEY constraint. We will create a new table order_status with a foreign key order_id that references the primary key of orders table.

postgres=# CREATE TABLE IF NOT EXISTS order_status (
             status_id INT,
             order_id INT,
             status VARCHAR(255) NOT NULL,
             is_completed BOOLEAN NOT NULL DEFAULT FALSE,
                PRIMARY KEY (status_id),
                FOREIGN KEY (order_id)
                REFERENCES orders (id)
                ON UPDATE RESTRICT ON DELETE CASCADE
               );

postgres=# \d order_status;
                  Table "public.order_status"
    Column    |          Type          |       Modifiers
--------------+------------------------+------------------------
 status_id    | integer                | not null
 order_id     | integer                |
 status       | character varying(255) | not null
 is_completed | boolean                | not null default false
Indexes:
    "order_status_pkey" PRIMARY KEY, btree (status_id)
Foreign-key constraints:
    "order_status_order_id_fkey" FOREIGN KEY (order_id) REFERENCES orders(id) ON UPDATE RESTRICT ON DELETE CASCADE

In the above query, we add a foreign key constraint while creating PostgreSQL table.

Hopefully, now you can easily create table in PostgreSQL.

Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!

How to Get Last Row Per Group in PostgreSQL

How to Limit Access to Database in PostgreSQL

How to Concatenate Strings in PostgreSQL