Many times you may need to create table in PostgreSQL. Here’s how to create table using PostgreSQL CREATE TABLE command.
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!
