Skip to content
Tutorials Center
My learning

Topics

Lesson 32 of 90 ยท Schema Design and Constraints

Copying and Comparing Tables

Copy PostgreSQL tables with CREATE TABLE AS, LIKE ... INCLUDING and SELECT INTO, then compare two tables with EXCEPT and FULL JOIN to find missing and changed rows.

Sooner or later you need a second copy of a table. Maybe you want a snapshot before a risky update, a scratch table for an experiment, a staging table that has the same shape as the real one, or a quick way to check that two tables hold the same data after a migration. PostgreSQL has several ways to copy, and they differ in one important respect: what they copy. Some copy only the rows, some copy only the structure, and some copy both, but none of them copies everything by default.

This lesson covers the copy commands (CREATE TABLE AS, SELECT INTO, CREATE TABLE ... LIKE), then the other half of the job: comparing two tables to find rows that are missing, extra or different. The PostgreSQL commands that need a real server are shown as read-only blocks. The live examples run in a small SQLite database in your browser, which supports the same EXCEPT and FULL JOIN queries you use for comparing.

Copying rows: CREATE TABLE AS

CREATE TABLE ... AS runs a query and stores the result in a brand-new table:

SQL
-- PostgreSQL
CREATE TABLE customers_backup AS
SELECT * FROM customers;

-- Copy only some rows and columns
CREATE TABLE vip_customers AS
SELECT id, email, country
FROM customers
WHERE lifetime_value > 5000;

-- Copy the structure only (no rows)
CREATE TABLE customers_empty AS
SELECT * FROM customers
WITH NO DATA;

The new table gets the column names and data types of the query result and the rows, and nothing else. It has no primary key, no indexes, no defaults, no NOT NULL constraints, no foreign keys and no triggers. That is fine for a snapshot, and a trap if you intend to use the copy as a real table. After copying you add what you need:

SQL
-- PostgreSQL
ALTER TABLE customers_backup ADD PRIMARY KEY (id);
CREATE INDEX ON customers_backup (email);

Two variations are worth knowing. CREATE TABLE IF NOT EXISTS ... AS skips the work when the table is already there. And CREATE TEMP TABLE ... AS makes a copy that disappears at the end of the session (see Temporary and Unlogged Tables); it is perfect for intermediate results in a long report.

CREATE TABLE AS and CREATE MATERIALIZED VIEW are close relatives. A materialized view also stores a query result, but it remembers the query and can be refreshed later with REFRESH MATERIALIZED VIEW. A table made with AS forgets where it came from. See Views and Materialized Views.

Copying structure: CREATE TABLE ... LIKE

LIKE copies the shape of an existing table. By default it copies the column names, types and NOT NULL constraints, and no rows:

SQL
-- PostgreSQL
CREATE TABLE orders_staging (LIKE orders);

You pick extra pieces with INCLUDING options:

Option What it also copies
INCLUDING DEFAULTS Column default expressions
INCLUDING GENERATED Generated column expressions
INCLUDING IDENTITY Identity column definitions
INCLUDING CONSTRAINTS CHECK constraints
INCLUDING INDEXES Indexes, and primary key, unique and exclusion constraints
INCLUDING STATISTICS Extended statistics objects
INCLUDING COMMENTS Column and constraint comments
INCLUDING STORAGE Storage settings such as the TOAST strategy
INCLUDING COMPRESSION Per-column compression methods
INCLUDING ALL All of the above

So the closest thing to a faithful structural clone is:

SQL
-- PostgreSQL
CREATE TABLE orders_copy (LIKE orders INCLUDING ALL);

Even then, foreign keys are never copied by LIKE, and neither are triggers or row-level security policies. A serial column's default is copied, but it still points at the original table's sequence, so both tables draw from the same counter. Identity columns, on the other hand, get a fresh sequence of their own when you use INCLUDING IDENTITY.

To copy the rows as well, a two-step recipe keeps the structure complete:

SQL
-- PostgreSQL
CREATE TABLE orders_copy (LIKE orders INCLUDING ALL);
INSERT INTO orders_copy SELECT * FROM orders;

Creating the table first and loading it afterwards means the indexes exist while you insert. For large tables it is faster to load first and create the indexes afterwards, as explained in Bulk Loading.

SELECT INTO

PostgreSQL also accepts the older SELECT ... INTO new_table form, borrowed from SQL Server:

SQL
-- PostgreSQL
SELECT id, email
INTO customers_snapshot
FROM customers
WHERE country = 'DE';

It behaves like CREATE TABLE AS: new table, no constraints, no indexes. Inside a PL/pgSQL function, however, SELECT ... INTO variable means something completely different: it stores the result in a variable (see PL/pgSQL Basics). For that reason, prefer CREATE TABLE AS in new code. It is standard SQL, and there is no ambiguity.

Copying between databases

A table lives in one database, and a SQL statement cannot reach into another database of the same cluster. Three common routes:

Terminal
# Copy one table's structure and data to another database with pg_dump
pg_dump --table=customers shop | psql shop_copy

# Export to a file and import it again (client side, works with any server)
psql shop -c "\copy customers TO 'customers.csv' CSV HEADER"
psql shop_copy -c "\copy customers FROM 'customers.csv' CSV HEADER"

The third route is a foreign data wrapper (postgres_fdw) that lets you run INSERT INTO local SELECT * FROM remote_table from one database against another.

What CREATE TABLE AS leaves behind

The next example builds a small table, copies it with CREATE TABLE AS, and reads back the definition SQLite stored for each. SQLite's CREATE TABLE AS has the same "column names and rows only" behavior as PostgreSQL's.

SQL What CREATE TABLE AS does not copy

Press Run (or Ctrl + Enter) to see the result here.

The original keeps its primary key, NOT NULL, default and CHECK; the copy is a bare list of columns. In PostgreSQL you would see the same difference with \d products and \d products_copy in psql.

Comparing two tables

After a copy, a migration or an ETL job, the question is "are these two tables the same?". Three set-based queries answer it.

Rows in A but not in B, with EXCEPT:

SQL
SELECT * FROM table_a
EXCEPT
SELECT * FROM table_b;

Run it in both directions to see the full difference. EXCEPT compares whole rows, treats two NULLs as equal (unlike =), and removes duplicates; EXCEPT ALL keeps duplicate counts, which matters when a table has no key.

Rows that match on a key but differ in values, with a join and IS DISTINCT FROM:

SQL
SELECT a.id
FROM table_a a
JOIN table_b b USING (id)
WHERE a.name IS DISTINCT FROM b.name
   OR a.price IS DISTINCT FROM b.price;

PostgreSQL's IS DISTINCT FROM treats NULL as a comparable value, so a change from NULL to 5 is detected and NULL versus NULL is not reported. SQLite spells the same operator IS NOT, which is what the live example uses.

Everything in one report, with a FULL JOIN on the key:

SQL
SELECT COALESCE(a.id, b.id) AS id,
       CASE
         WHEN a.id IS NULL THEN 'only in B'
         WHEN b.id IS NULL THEN 'only in A'
         ELSE 'changed'
       END AS difference
FROM table_a a
FULL JOIN table_b b ON a.id = b.id
WHERE a.id IS NULL
   OR b.id IS NULL
   OR a.name IS DISTINCT FROM b.name;
SQL A full diff of two tables

Press Run (or Ctrl + Enter) to see the result here.

Row 1 is unchanged and so is not listed; row 2 has a new price, row 3 disappeared, row 4 went from NULL to a value, and row 5 is new. A plain a.price <> b.price would miss row 4 (comparing with NULL gives unknown), which is exactly the kind of silent miss that IS DISTINCT FROM prevents.

A second example shows EXCEPT and INTERSECT on the same data, in both directions:

SQL EXCEPT in both directions

Press Run (or Ctrl + Enter) to see the result here.

The NULL appears under "in both": set operators treat two NULLs as the same value.

When tables are too large for a full join, compare a fingerprint first. In PostgreSQL a whole row can be hashed with md5(t::text), and you can aggregate counts and checksums per range of ids to find which ranges differ before looking at individual rows:

SQL
-- PostgreSQL
SELECT id / 10000 AS bucket,
       count(*) AS row_count,
       md5(string_agg(t::text, '|' ORDER BY id)) AS checksum
FROM customers t
GROUP BY bucket
ORDER BY bucket;

Run it on both tables and compare the two outputs; only buckets with a different row_count or checksum need a row-by-row look.

Do not trust count(*) alone, and remember that EXCEPT ignores row order and removes duplicates. Two tables can have the same count and different content, or the same distinct rows with different duplicate counts.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. You run CREATE TABLE copy AS SELECT * FROM orders;. Which of these does the new table have?

    CREATE TABLE AS stores only the result of the query. Keys, indexes, defaults and other constraints are not copied, so you add them afterwards.
  2. 2. Which statement gives you an empty table with the same columns, defaults, check constraints and indexes as orders?

    LIKE ... INCLUDING ALL copies defaults, constraints, indexes and more. Plain LIKE copies only columns and NOT NULL, and the AS forms copy no indexes or defaults.
  3. 3. Why prefer CREATE TABLE AS over SELECT ... INTO in new PostgreSQL code?

    It still works in plain SQL, but CREATE TABLE AS is standard SQL and has no second meaning inside functions.
  4. 4. Two tables are compared with a.price <> b.price. Row 4 has price NULL in table a and 2.00 in table b. What happens?

    A comparison with NULL yields unknown, and WHERE keeps only true rows. Use IS DISTINCT FROM (IS NOT in SQLite) to treat NULL as a value.

Summary

  • CREATE TABLE ... AS (and SELECT ... INTO) copy a query result: columns, types and rows, but no keys, indexes, defaults or constraints. WITH NO DATA copies only the shape.
  • CREATE TABLE ... (LIKE source INCLUDING ALL) copies structure, defaults, check constraints and indexes, but never rows, foreign keys or triggers.
  • To clone a table fully, create it with LIKE ... INCLUDING ALL, then INSERT ... SELECT.
  • Compare tables with EXCEPT in both directions, a join with IS DISTINCT FROM for changed values, or a FULL JOIN for one combined report.
  • For huge tables, compare per-bucket row counts and checksums before looking at rows.

Next: Constraints in Depth, the rules that CREATE TABLE AS forgets to copy.