Copy Table

PostgreSQL provides several CREATE TABLE ... AS forms for copying a table. All of them copy the column definitions and data you specify, but none of them copy indexes, primary keys, foreign keys, or other constraints. Those must be added manually after the copy.

Three ways to copy a table

Copy structure and all data:

CREATE TABLE new_table AS TABLE existing_table;

Copy structure only, with no rows:

CREATE TABLE new_table AS TABLE existing_table WITH NO DATA;

Copy structure and a filtered subset of rows:

CREATE TABLE new_table AS
SELECT * FROM existing_table
WHERE condition;

Step-by-step example

Create the source table and insert rows:

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

INSERT INTO contacts (first_name, last_name, email)
VALUES
  ('John',  'Doe',     '[email protected]'),
  ('David', 'William', '[email protected]')
RETURNING *;

-- Output:
--  id | first_name | last_name | email
-- ----+------------+-----------+---------------------------
--   1 | John       | Doe       | [email protected]
--   2 | David      | William   | [email protected]

Copy the table including its data:

CREATE TABLE contact_backup AS TABLE contacts;

Verify the copied data:

SELECT * FROM contact_backup;

-- Output:
--  id | first_name | last_name | email
-- ----+------------+-----------+---------------------------
--   1 | John       | Doe       | [email protected]
--   2 | David      | William   | [email protected]

Inspect the copy — notice that indexes are not present:

d contact_backup

-- Table "public.contact_backup"
--    Column   | Type    | Nullable
-- ------------+---------+---------
--  id         | integer |
--  first_name | varchar |
--  last_name  | varchar |
--  email      | varchar |
-- (No indexes listed)

Restore the primary key and unique constraint manually:

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

Production tips

• Use CREATE TABLE ... AS TABLE ... WITH NO DATA to create an empty clone for testing schema changes without duplicating large data sets.
• After copying, always run d table_name to confirm which indexes and constraints are missing and need to be added.
• For large tables, consider creating indexes with CREATE INDEX CONCURRENTLY after the copy to avoid long locks.
• If you need an exact structural clone including all constraints and indexes, use pg_dump -t source_table | psql target_db instead.
• Copying with a WHERE clause is useful for creating audit or archive tables that hold only a time-bounded subset of rows.