null value in column "id" violates not-null constraint

ERROR: null value in column "id" of relation "users" violates not-null constraint (SQLSTATE 23502) on an insert that did not mean to set an id has, in practice, two causes. Either the column has no default — no serial, no identity, no default gen_random_uuid() — so nothing fills it in, or the insert sends NULL explicitly, which a default does not replace. The DETAIL line shows the row as PostgreSQL saw it — as the table owner: Failing row contains (null, anna). A role with INSERT but no SELECT got only the columns it inserted, Failing row contains (name) = (a)., and under row-level security there was no DETAIL line at all.

We measured each cause on PostgreSQL 17.11 and 16.15, and the message itself on 14, 13, 12 and 10. Up to 12 the message read null value in column "id" violates not-null constraint; from 13 on it names the table: … of relation "users" ….

Why does the id column have no default?

Because something created or changed the table without it. The ways we found:

How the table came about	What it got	Insert without an id
`id integer primary key`, written by hand or by a migration that forgot `serial`	no default	`23502`
`create table copy (like users)`	`NOT NULL` kept; default, identity and primary key dropped	`23502`
`create table copy (like users including all)`, source `serial`	the default, pointing at the source’s sequence	works, but takes numbers from the original table’s counter (id 2); after `drop table users cascade` the copy’s default went with it (`drop cascades to default value for column id of table copy`) and the next insert gave `23502`
the same, source an identity column	its own identity	works (id 1)
`create table copy as select * from users`	no default, no `NOT NULL`, no primary key	succeeds with id = NULL — no error at all
`pg_restore -t users` from a schema-only dump	`NOT NULL` kept; default and primary key not restored	`23502`
`alter table users alter column id drop default` (some migrations do)	no default	`23502`
`id uuid primary key`	no default	`23502`; with `default gen_random_uuid()` it worked on 13 and later (on 12 and older only after `create extension pgcrypto`)

The CREATE TABLE AS row is the worse outcome: the copy accepts rows with no id, and the error only appears later, when someone adds the primary key. A full restore, by contrast, kept the default even where CREATE SEQUENCE failed because the sequence already existed (pg_restore exited with 1, and the default pointed at the sequence that was already there): the insert got id 1. A restore of one table with pg_restore -t is what leaves the default behind — not pg_dump -t, whose dump did contain the sequence, the default and the primary key.

A BEFORE INSERT trigger that sets NEW.id hides all of this: with one in place, an insert without an id, and one with an explicit NULL, both succeeded although the column had no default.

Why does an explicit NULL not use the default?

Because a default is only used for a column that is left out, or given DEFAULT. An insert that names id and passes NULL — which is what an ORM can send when its model does not mark the key as generated and the attribute is still None — gets exactly that NULL:

Statement	`serial`	identity `by default`	identity `always`
`insert into users (name) values ('anna')`	works	works	works
`insert into users (id, name) values (default, 'anna')`	works	works	works
`insert into users default values`	works	works	works
`insert into users (id, name) values (null, 'anna')`	`23502`	`23502`	`428C9: cannot insert a non-DEFAULT value into column "id"`
`insert into users (id, name) select null, 'anna'`	`23502`	`23502`	`428C9`
`… overriding user value values (null, 'anna')`	—	works	works
`… overriding system value values (null, 'anna')`	—	—	`23502`

So with generated always the same ORM bug shows up as a different message (on 13 and older: cannot insert into column "id"). For an identity column, OVERRIDING USER VALUE makes PostgreSQL ignore the NULL and use the identity. on conflict do nothing does not help: a NULL never conflicts, so the insert is attempted and fails the same way. The fix in the application is to leave the key out of the insert, or to send DEFAULT; the fix in the schema below only helps when the default is the thing that is missing.

From the error to the fix

Does \d users show a default or identity on id?

NoThe schema lost itAdd an identity (integer keys) or default gen_random_uuid() (uuid keys), and move the identity past max(id) — without the restart, the next inserts collide with existing ids.
Yes, nextval(…) or identityThe insert sends NULLLeave id out of the insert or send DEFAULT; check what the ORM puts in the column list.

Each branch measured on PostgreSQL 17.11. A BEFORE INSERT trigger that fills the id prevents the error in both cases.

How do you add the missing default to a table with data?

Each fix measured on a table that already held ids 1 to 3:

Fix	Next insert
`alter table users alter column id add generated by default as identity`	fails: the identity starts at 1, so the inserts collided on 1, 2 and 3 (`duplicate key`) before the fourth got id 4
the same, then `alter table users alter column id restart with 4`	id 4
`create sequence users_id_seq owned by users.id`, `alter column id set default nextval('users_id_seq')`, then `select setval('users_id_seq', coalesce(max(id), 0) + 1, false) from users`	id 4; without the `setval`, id 1 and a `duplicate key` — and the runtime role then needs `grant usage` on the new sequence
from `serial` to identity: `drop default`, `drop sequence`, `add generated by default as identity`, `restart with` max + 1	id 4

The restart is the step that is easy to forget, and forgetting it turns this error into the next one: why a counter behind the table gives duplicate key value violates unique constraint "users_pkey", and how to find every such counter, is in duplicate key value violates unique constraint. An identity column — new, or added to an existing table as above — also needs no grant on its sequence for inserts; the create sequence fix does, and without grant usage the runtime role got permission denied for sequence, the error measured in permission denied for sequence. A uuid key cannot get an identity (identity column type must be smallint, integer, or bigint): give it default gen_random_uuid(). And a CREATE TABLE AS copy first needs set not null, which fails while rows with a NULL id exist.

How do you find primary keys without a default?

select n.nspname as schema, c.relname as table_name, a.attname as column_name,
       format_type(a.atttypid, a.atttypmod) as type
from pg_index i
join pg_class c       on c.oid = i.indrelid
join pg_namespace n   on n.oid = c.relnamespace
join pg_attribute a   on a.attrelid = i.indrelid and a.attnum = any (i.indkey)
left join pg_attrdef d on d.adrelid = a.attrelid and d.adnum = a.attnum
where i.indisprimary
  and a.attidentity = ''
  and d.adrelid is null
  and n.nspname not in ('pg_catalog', 'information_schema', 'pg_toast')
  and c.relkind in ('r', 'p')
order by 1, 2, 3;

On a test schema it found the table without a default, the uuid key without a default and the LIKE copy, and skipped the serial, identity and default gen_random_uuid() tables. It also listed both columns of a composite key (tenant_id, id): in a multi-tenant schema that can be intended — the application supplies both — so read the list rather than fix every row. It only looks at primary keys: a CREATE TABLE AS copy has none and is not listed, and a LIKE … INCLUDING ALL copy that shares the source’s sequence has a default and is not listed either. A table whose id is filled by a trigger is listed although it never fails, and so is a partition attached with ATTACH PARTITION without its own default (inserts through the parent worked, direct inserts into the partition failed). Without the pg_toast filter it also listed every TOAST table’s chunk_id and chunk_seq.

The same message for another column (null value in column "name" of relation "users", with COLUMN NAME: name in verbose mode) has nothing to do with defaults: the application did not send a value for a required field.

What we did not measure

Specific ORMs and how they build their column lists.

All measurements are our own, on PostgreSQL 17.11, 16.15, 14.24, 13.23, 12.22, 11.22 and 10.23 in containers, on 22 October 2026.