Copy Table in PostgreSQL

PostgreSQL supports CREATE TABLE AS to copy table structure with or without data.

Use the following syntax to copy the table completely including table structure and data.

CREATE TABLE new_table_name AS TABLE existing_table;

To copy only table structure without data, WITH NO DATA clause can be used with CREATE TABLE AS statement.

CREATE TABLE new_table_name AS
TABLE existing_table
[WITH NO DATA];

To copy a table with a partial set of data use the WHERE clause with the SELECT query. The WHERE clause will restrict the set of rows.

CREATE TABLE new_table_name AS
SELECT * FROM existing_table
WHERE conditions;

Note that all of the above statements will copy table structure and/or copy data but will not copy indexes, constraints, or any other database objects of an existing table.

Let's use the following Person table to understand how to copy a table.

Use the following command to copy the Person table to the Employee table with structure and data.

CREATE TABLE EMPLOYEE AS
TABLE PERSON;

Let's check whether the structure and data copied to the Employee table or not

Now, let's create a backup table from the PERSON table without data using WITH NO DATA option.

CREATE TABLE PERSON_BKP AS
TABLE PERSON
WITH NO DATA;

The above statement will only copy the structure of the PERSON table and create the PERSON_BKP table, as shown below.