Create Tables in PostgreSQL

Here you will learn:

• Create Table using CREATE TABLE statement
• Create Table using CREATE TABLE AS statement
• Create Temporary Table

Create Table using CREATE TABLE statement

The table is a basic structure in relation database that stores the structured data. The CREATE TABLE statement is used to create a new table in the database. The following is the syntax to create a new table.

CREATE TABLE [IF NOT EXISTS] <table_name> (
   <column1> <data_type(length)> [column_contraint],
   <column2> <data_type(length)> [column_contraint],
...
   <columnN> <data_type(length)> [column_contraint],
   [table_constraints]
);

In the above syntax,

• After CREATE TABLE you can specify optional IF NOT EXISTS clause, which will create a table only if it does not exist. If a table already exists, Postgres will only give a warning instead of an error and skip creating a new table.
• The table_name is the unique name of a table which you want to create.
• In the breckt, specify the list of column names along with their data types. The data_type is the type of data a column is going to be stored e.g string, integer, date, etc. Specify the optional [comlumn_constraint] such as NOT NULL, Check, Unique, Primary Key, Foreign Key etc.
• Specify optional table-level constraints, e.g. NULL, Primary key, foreign key, check constraints.

The following creates a new person table in PostgreSQL database using psql.

CREATE TABLE IF NOT EXISTS person (
    Id 	INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    gender CHAR(1),
    birthdate DATE,
    email_id VARCHAR(100) UNIQUE,
    country_of_birth VARCHAR(50)
);

The following creates a new table using psql.

Use \d or \dt command to list all the tables in the current database in psql.

Use the \d table-name command to see the structure of the specified table. It will list all columns of tables, with their data types and constraints.

CREATE TABLE AS

Use CREATE TABLE AS statement to create a new table with data from the specified SELECT query. The columns and data types of the table will be the same as a result of the SELECT query. It will add the records returned from the SELECT query into a table.

CREATE TABLE [IF NOT EXISTS] <table_name> 
[(<column1>, <column2>, ...)]
AS
<Query>;  

The following will create the EMPLOYEE table with the same column names and data types as SELECT query that selects data from the person table.

CREATE TABLE employee 
AS
SELECT * FROM person;

Use \dt and \d employee command to see the new tables and table structure, as shown below.

If you want to create a table with a selected column list then, you can select only required columns in the SELECT clause.

CREATE TABLE employee 
AS SELECT ID, FIRST_NAME, LAST_NAME, GENDER FROM person;

If you want column names to be different from SELECT query columns you can specify new columns list.

CREATE TABLE employee(EmployeeId, FirstName, LastName, Gender) 
AS SELECT ID, FIRST_NAME, LAST_NAME, GENDER FROM person;

To avoid the error that occurs during table creation if a table already exists, you can specify IF NOT EXISTS clause while creating the table.

CREATE TABLE IF NOT EXISTS employee 
AS SELECT * FROM person;

Create Temporary Tables

Postgres allows us to create temporary tables. The temporary tables exist only for the duration of the database session. As soon as we disconnect from the database session, Postgres drops all the temporary tables.

Use the CREATE TEMPORARY TABLE statement to create a new temporary table in the current database. You can use TEMPORARY or TEMP keyword interchangeably.

CREATE TEMPORARY TABLE [IF NOT EXISTS] <table_name> (
   <column1> <data_type(length)> [column_contraint],
   <column2> <data_type(length)> [column_contraint],
...
   <columnN> <data_type(length)> [column_contraint],
   [table_constraints]
);

The following will create a temporary table myTempTble. Note that we have used IF NOT EXISTS statement, so if a table already exists, then Postgres will just give a warning and skip the table creation.

CREATE TEMPORARY TABLE IF NOT EXISTS myTempTble(
    Id 	INT,
    name VARCHAR(50)
);

• Postgres creates temporary tables in a separate schema, hence you can not specify schema name while creating a temporary table.
• A temporary table created in one session is restricted only to that session. Another session to the same database cannot see or access the temporary table created by the previous session.
• Temporary tables are automatically dropped at the end of the database session.
• A temporary table can have the same name as a permanent table in the database, although it's not advisable to do so. In that case, whenever you try to access the table, it will always refer temporary table and not the permanent table. The permanent table will only be accessible once the temporary table is dropped.