How to Duplicate a Table in PostgreSQL
Last updated July 24, 2026 · By the SaturnSQL team
CREATE TABLE new (LIKE old INCLUDING ALL) copies structure, indexes, and defaults; INSERT ... SELECT copies the rows. CREATE TABLE AS SELECT is shorter but loses indexes and constraints.
Structure + data with indexes
INCLUDING ALL brings across indexes, defaults, constraints, comments, and storage settings, which is what you want for a backup you might swap back in. It does not copy foreign keys or ownership. The INSERT then copies the rows, costing time and disk proportional to the table size.
CREATE TABLE orders_backup (LIKE orders INCLUDING ALL);
INSERT INTO orders_backup SELECT * FROM orders;Quick copy, data only
CREATE TABLE AS is a single statement and the fastest way to snapshot data, but the result is a bare table: no indexes, no primary key, no constraints, no defaults. Add WITH NO DATA if you want just the column types without the rows.
CREATE TABLE orders_copy AS SELECT * FROM orders;Run this in SaturnSQL
SaturnSQL is a browser-based SQL editor for teams: shared query library, schema-aware autocomplete, and scheduled exports to Google Sheets and Slack.
Try it free