Copy Table in PostgreSQL

PostgreSQL provides several ways to copy a table using CREATE TABLE ... AS. All forms transfer column definitions and data, but none copy indexes, primary keys, foreign key constraints, or check constraints — you must add those manually after the copy.

Syntax

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;

Practical Example

Create and populate a source table:

CREATE TABLE products (
  product_id  SERIAL PRIMARY KEY,
  sku         VARCHAR(50)    NOT NULL UNIQUE,
  name        VARCHAR(200)   NOT NULL,
  unit_price  NUMERIC(10,2)  NOT NULL,
  is_active   BOOLEAN        NOT NULL DEFAULT TRUE
);

INSERT INTO products (sku, name, unit_price)
VALUES
  ('WID-001', 'Basic Widget',    9.99),
  ('WID-002', 'Premium Widget', 24.99),
  ('WID-003', 'Deluxe Widget',  49.99),
  ('WID-004', 'Budget Widget',   4.99);

Copy the full table:

CREATE TABLE products_backup AS TABLE products;

Verify the data was copied:

SELECT * FROM products_backup ORDER BY product_id;

Inspect the copy — note the absence of indexes:

\d products_backup

         Table "public.products_backup"
   Column   |          Type          | Nullable
------------+------------------------+----------
 product_id | integer                |
 sku        | character varying(50)  |
 name       | character varying(200) |
 unit_price | numeric(10,2)          |
 is_active  | boolean                |
(No indexes listed)

Recreate the primary key and unique constraint on the copy:

ALTER TABLE products_backup ADD PRIMARY KEY (product_id);
ALTER TABLE products_backup ADD UNIQUE (sku);

Create an empty structural clone for schema testing:

CREATE TABLE products_staging AS TABLE products WITH NO DATA;

Copy only active products:

CREATE TABLE active_products AS
SELECT *
FROM products
WHERE is_active = TRUE;

Full Structural Clone with pg_dump

When you need a complete copy including all constraints and indexes:

-- In the shell:
-- pg_dump -t products mydb | psql mydb

-- Or dump to a file and restore under a new name:
-- pg_dump -t products -f products_schema.sql mydb

For a rename-based clone entirely in SQL, script each constraint:

CREATE TABLE products_v2 AS TABLE products;
ALTER TABLE products_v2 ADD PRIMARY KEY (product_id);
ALTER TABLE products_v2 ADD UNIQUE (sku);
ALTER TABLE products_v2 ALTER COLUMN is_active SET DEFAULT TRUE;
ALTER TABLE products_v2 ALTER COLUMN unit_price SET NOT NULL;
-- etc.

Testing with Vela

When creating archive or staging copies of production tables, Vela database branching provides an instant full copy of the entire database for free — no CREATE TABLE AS TABLE required. Use branching for schema testing and migration rehearsal; reserve CREATE TABLE AS for runtime copies (ETL snapshots, audit tables, reporting caches) within a single database.

Production Tips

• After copying, always run \d new_table to confirm which indexes and constraints are missing and must be added.
• For large tables, use CREATE INDEX CONCURRENTLY after the copy to avoid long lock windows.
• CREATE TABLE AS TABLE ... WITH NO DATA is useful for creating an empty schema clone for load testing or migration rehearsal.
• If you need a full structural clone with all constraints and indexes, use pg_dump — CREATE TABLE AS only copies columns and data.
• Copying with a WHERE clause is ideal for creating time-bounded archive tables (e.g., rows from a specific year) before purging old data from the main table.
• The copy does not inherit SERIAL or identity column behavior — the sequence is not carried over. Add a new sequence or identity constraint if the copy needs auto-increment IDs.