PostgreSQL – Create Table

A Table is a database object that holds data. You must specify table name, column names and their data types to create a table. Below is the syntax to create a new table in Postgres database.

CREATE TABLE tablename
(column1 datatype1,
column2 datatype2,
column3 datatype3
);

Example:

CREATE TABLE person
(first_name varchar(100),
last_name varchar(100),
age smallint
);

If the table named ‘person’ already exists, the above ‘create table’ statement will throw an error. You can use ‘if not exists’ clause to not receive any errors.

CREATE TABLE person IF NOT EXISTS
(first_name varchar(100),
last_name varchar(100),
age smallint
);

In this case, the table will be created only if it does not exist; Above SQL statement does not throw an error if the table already exists.

Use the below syntax to create a table (with data) from an existing table using a sql query:

CREATE TABLE tablename       
AS                           
SELECT * from existing_table;

Below is the syntax to create a table like an existing table without copying data:

CREATE TABLE tablename AS
SELECT * FROM table1
WITH NO DATA;