The Collection Part 2 #Postgres_Copy_Table

Publish on Jun 19, 2017 by Alfin F

To copy a table completely, including both table structure and data, you use the following statement:

CREATE TABLE new_table AS TABLE existing_table;

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

CREATE TABLE new_table AS TABLE existing_table WITH NO DATA;

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

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 that all the statement above copy table structure and data but do not copy indexes and constraints of the existing table.

PostgreSQL copy table example

The following statement creates a new table named contacts for the demonstration:

CREATE TABLE contacts(

    id SERIAL PRIMARY KEY,

    first_name VARCHAR NOT NULL,

    last_name VARCHAR NOT NULL,

    email VARCHAR NOT NULL UNIQUE

);

In this table, we have two indexes: one index for the primary key and another for the UNIQUE constraint.

Let’s insert some rows into the contacts table:

INSERT INTOcontacts(first_name,last_name,email) VALUES('John','Doe','john.doe@postgresqltutorial.com'),

      ('David','William','david.william@postgresqltutorial.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;

This statement creates a new table named contact_backup whose structure is the same as the contacts table. In addition, it copies data from the contacts table to the contact_backup table.

Let’s check the data of the contact_backup table by using the following SELECT statement:

It returns two rows as expected

To examine the structure of the contact_backup table:

As you can see in the output, the structure of the contact_backup table is the same as the contacts table except for the indexes.

To add the primary key and UNIQUE constraints to the contact_backup table, you use the following ALTER TABLE statements:

ALTER TABLE contact_backup ADD PRIMARY KEY(id);

ALTER TABLE contact_backup ADD UNIQUE(email);

To view structure of the contact_backup table again, you use \d command:

In this tutorial, you have learned how to copy an existing table to a new one using the various forms PostgreSQL copy table statement.

source : postgresqltutorial.com