---
title: "How a column change misroutes positional INSERT and COPY | Proofline"
description: "An INSERT or COPY without a column list matches values to columns by position. Adding a column at the end breaks COPY loudly."
url: https://proofline.sh/learn/postgres-positional-insert-column-changes/
---

[Learning center](/learn/)

# 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.

Schema conflicts The Proofline team November 20, 2026 3 min read

## 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`](https://www.postgresql.org/docs/16/sql-copy.html) 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;
```

| id | kind | source |
| --- | --- | --- |
| 5 | signup | {} |

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](/learn/postgres-migration-rollback/) 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](/learn/postgres-migration-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](/free-code-review/).

[Start free with GitHub](https://proofline.sh/orgs/plan?offer=free_month)

## Related reading

-   ### [Fix "cached plan must not change result type" after adding a PostgreSQL column](/learn/postgres-cached-plan-result-type/)
    
    Schema conflicts The Proofline team November 20, 2026 3 min read
    
-   ### [How to test a PostgreSQL migration before rolling back the app](/learn/postgres-migration-rollback/)
    
    Breaking migrations The Proofline team November 18, 2026 3 min read
