To copy a table completely, including both table structure and data, you use the following statement:
CREATE TABLE new_table AS TABLE existing_table;
CREATE TABLE new_table AS TABLE existing_table WITH NO DATA;
CREATE TABLE new_table AS SELECT * FROM existing_table WHERE condition;
Note that all the statement above copy table structure and data but do not copy indexes and constraints of the existing table.
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');
contacts
to a new table, for example, contacts_backup
table, you use the following statement:CREATE TABLE contact_backupAS 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);
contact_backup
table again, you use \d
command: