When working with PostgreSQL, there are times you need an exact copy of an existing table — including its structure, indexes, constraints, and data. This is especially useful for tasks like testing, archival, or experimenting without touching the original dataset.

Here’s a step-by-step guide to fully clone a table in PostgreSQL.

Clone the table structure

PostgreSQL provides the CREATE TABLE ... (LIKE ...) syntax, which lets you replicate a table’s schema with high fidelity:

CREATE TABLE new_table
(LIKE original_table INCLUDING ALL);

This creates new_table with:

• All columns and data types
• Default values
• NOT NULL and CHECK constraints
• Indexes
• Column and table comments
• Storage parameters

Foreign key constraints to other tables aren’t copied. You’ll need to recreate those manually if they’re important for your use case.

Copy the data

Once the structure is in place, use a simple INSERT INTO ... SELECT to copy the contents:

INSERT INTO new_table
SELECT * FROM original_table;

With large tables, be aware that this step can take a while.

Watch out for sequences

If your original table uses serial or bigserial columns, they rely on sequences for auto-incrementing. By default, these sequences are shared between both tables — which can lead to conflicts.

To give new_table its own sequence:

-- 1. Create a new sequence
CREATE SEQUENCE new_table_id_seq;

-- 2. Set it as the default for the ID column
ALTER TABLE new_table
ALTER COLUMN id SET DEFAULT nextval('new_table_id_seq');

-- 3. Sync it to the current max value (optional but recommended)
SELECT setval('new_table_id_seq', COALESCE((SELECT MAX(id) FROM new_table), 1));