PostgreSQL Create and Drop Table

The PostgreSQL table is created using the CREATE TABLE command and initially, it is an empty table. The owner of the table will the user who is creating it. If we define the table with a schema name then the table will be created in that schema, otherwise, the table will be created in the current schema. Each table in the PostgreSQL database is uniquely defined and can't be duplicated.

1. PostgreSQL Create Table Command

In this section, we will create a table named PRODUCTS_DETAIL in the current schema that is postgres and run the psql commands \d to check its definition.


Syntax:

The following is the syntax of the PostgreSQL Create table command.

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | unlogged ] TABLE [ IF NOT EXISTS ] tablename (
[  { column_name datatype [ COMPRESSION compressionmethod ] [ COLLATE collation ] [ columnconstraint [ ... ] ]
    | tableconstraint
    | LIKE sourcetable [ likeoption ... ] }
    [, ... ]
] )
[ INHERITS ( parent_table [, ... ] ) ]
[ PARTITION BY { RANGE | LIST | HASH } ( { columnname | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]
[ USING method ]
[ WITH ( storage_parameter [= value] [, ... ] ) | without oids ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespacename ]


Command:

postgres=# CREATE TABLE products_detail (
    product_number INTEGER,
    product_name TEXT,
    product_price NUMERIC
);
postgres=# \d products_detail


Output:

In the below output we can see the table has been created with the name PRODUCTS_DETAIL and we can describe it using the \d PRODUCTS_DETAIL command.


postgresql create table command


2. PostgreSQL Create Table As Command

We can create a new table by copying the data and structure of the existing table. If we mention WITH NO DATA then the only structure will be copied and if we don't mention WITH NO DATA then the data, as well as structure, will be copied.

In the below example, we will create a new table called NEW_PRODUCTS_DETAIL by copying the data and structure from an old table named PRODUCTS_DETAIL.


Syntax with Data:

CREATE TABLE new_table AS table existing_table;

Syntax without Data:

CREATE TABLE new_table AS table existing_table WITH no data;

Command:

postgres=# CREATE TABLE new_products_detail AS table products_detail;
postgres=# \d products_detail

Output:

In the below output, we can see that the new table NEW_PRODUCTS_DETAIL has been created by copying the data and structure from an old table named PRODUCTS_DETAIL.


postgresql create table as command


3. PostgreSQL Drop Table Command

The PostgreSQL Drop Table command is used to drop the existing table. This command will drop the table as well as the structure of the table.

In the following section, we will drop the table named NEW_PRODUCTS_DETAIL using the Drop Table command.


Syntax:

DROP TABLE [ IF EXISTS ] table_name [, ...] [ CASCADE | RESTRICT ]

Command:

postgres=# DROP TABLE new_products_detail;

Output:

We can verify from the below output that the table name new_products_detail has been dropped from the PostgreSQL database.


postgresql drop table as command