PostgreSQL – Copy Table

PostgreSQL allows copying an existing table including the table structure and data by using various forms of PostgreSQL copy table statement. To copy a table completely, including both table structure and data use the below statement.

Syntax:
CREATE TABLE new_table AS 
TABLE existing_table;

To copy a table structure without data, users need to add the WITH NO DATA clause to the CREATE TABLE statement as follows:

Syntax:
CREATE TABLE new_table AS 
TABLE existing_table 
WITH NO DATA;

To copy a table with partial data from an existing table, users can use the following statement:

Syntax:
CREATE TABLE new_table AS 
SELECT
*
FROM
    existing_table
WHERE
    condition;

The condition in the WHERE clause of the query defines which rows of the existing table will be copied to the new table. Note: All the statements above copy table structure and data but do not copy indexes and constraints of the existing table. Example: Creates a new table named contacts for the demonstration using the below statement:

CREATE TABLE contacts(
    id SERIAL PRIMARY KEY,
    first_name VARCHAR NOT NULL,
    last_name VARCHAR NOT NULL,
    email VARCHAR NOT NULL UNIQUE
);

Now let’s add some data to the contacts table using the below statement:

INSERT INTO contacts(first_name, last_name, email) 
VALUES('Raju', 'Kumar', 'rajukumar@gmail.com'),
      ('Nikhil', 'Aggarwal', 'nikhil@gmail.com');

To copy the contacts to a new table, for example, contacts_backup table, you use the following statement:

CREATE TABLE contact_backup 
AS TABLE contacts;

To verify the above use the below statement:

SELECT * FROM contact_backup;

Output: We can also check the data type and structure of the contact_backup table using the below command:

\d contact_backup;

It will result in the following: