Open In App

PostgreSQL – Copy Table

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Share
Report issue
Report

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:


Last Updated : 13 Mar, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads