Learning center

How a column change misroutes positional INSERT and COPY

An INSERT or COPY without a column list matches values to columns by position. Adding a column at the end breaks COPY loudly. Dropping a column in the middle makes the next positional INSERT write its values into the wrong columns, and the statement succeeds. Find the writers that omit the column list before you change the table.

An events table and its loaders#

An events table is filled by the application, a nightly CSV import and a few test fixtures. The application names its columns. The import and the fixtures do not. We ran this on PostgreSQL 16.15 in psql.

CREATE TABLE events (id integer PRIMARY KEY, kind text NOT NULL, payload text);
INSERT INTO events VALUES (1, 'signup', '{}');

Adding a column breaks COPY#

A migration adds a nullable source column. The positional INSERT keeps working, because PostgreSQL fills trailing columns that the statement does not supply with their defaults. The CSV import does not:

ALTER TABLE events ADD COLUMN source text;

INSERT INTO events VALUES (2, 'login', '{}');
-- INSERT 0 1

\copy events FROM stdin WITH (FORMAT csv)
3,logout,{}
\.
ERROR:  missing data for column "source"
CONTEXT:  COPY events, line 1: "3,logout,{}"

COPY without a column list expects a field for every column. Naming the columns fixes it, and the column list then also documents what the file contains:

\copy events (id, kind, payload) FROM stdin WITH (FORMAT csv)

A NOT NULL column without a default fails differently. It cannot be added while rows exist, and once added, a positional INSERT that does not reach it fails with null value in column "tenant_id" ... violates not-null constraint. Both failures are loud, and both stop a job that was working yesterday.

Dropping a column misroutes the values#

The quieter case comes from a drop. Later the team removes payload, which no longer has a reader. The table now has id, kind, source. A fixture, or a script written before the drop, still inserts three values by position:

ALTER TABLE events DROP COLUMN payload;
INSERT INTO events VALUES (5, 'signup', '{}');
-- INSERT 0 1

SELECT id, kind, source FROM events WHERE id = 5;
idkindsource
5signup{}

The statement succeeded. '{}' was meant for payload and now sits in source, a column whose type it happens to match. If the values had not matched the column types, the insert would have failed. Here nothing failed, and a report that groups events by source now has a source called {}.

Find the positional writers#

Search the code, scripts and job definitions that write to the table for these patterns:

  • INSERT INTO table VALUES and INSERT INTO table SELECT without a column list after the table name.
  • COPY table FROM and \copy table FROM without a column list.
  • INSERT INTO table SELECT * FROM other_table, which depends on both tables keeping the same column order.
  • Fixture loaders and seed files that emit positional rows.

Then add column lists to each one before the migration that adds or removes columns. A dropped column also leaves its value in the old release's named INSERT, which then fails because the column no longer exists. The column rename rollback exercise covers that kind of incompatibility.

What to rehearse#

Run every writer that does not belong to the application against a staging copy of the migrated schema: the import job with yesterday's file, the seed script, the fixtures used by other repositories' tests. Then read back one row from each and compare it with the input. The rehearsal passes when each value lands in the column it was meant for. A clean exit is not enough, because the misrouted insert above also exited cleanly.

Find the other changes that need a rehearsal in which PostgreSQL migration needs a rehearsal.

One month of free code review.

New organizations get one calendar month of hosted code review for private and public GitHub repositories. No card or cloud connection required. Eligibility and fair-use terms.

Start free with GitHub

Related reading