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:
Contact Us