Copy table

Copying a table completely, including both table structure and data

The following expression is used for

CREATE TABLE new_table_name AS TABLE copy_table_name;

To copy the table structure without data,

CREATE TABLE new_table_name AS TABLE copy_table_name
WITH NO DATA;

To copy some of the data from an existing table and create a new table;

CREATE TABLE new_table_name AS SELECT * FROM copy_table_name 
WHERE condition;

Example:

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

INSERT INTO iletisim(first_name, last_name, email)
VALUES(‘Ali’,’Yılmaz’,’ayılmaz@postgres.com’),
(‘Davut’,’Yıldırım’,’davudyıl @postgres.com’);

CREATE TABLE iletisim_backup AS TABLE iletisim;

As seen in the picture, the structure of the iletisim_backup table is the same as the iletisim table, except for the indexes.

Execute the following ALTER TABLE statements to add the primary key and UNIQUE constraints to the table iletisim_backup.

ALTER TABLE iletisim_backup ADD PRIMARY KEY(id);
ALTER TABLE iletisim_backup ADD UNIQUE(email);