Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

INSERT fails because the id column receives null

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

Short answer

The id column is NOT NULL, and nothing filled it in. A plain integer primary key has no default, so an insert that leaves it out stores null and fails. Make the column an identity column or give it a sequence default, or stop sending an explicit null from the application.

  • Applies to PostgreSQL 16, PostgreSQL 17, PostgreSQL 18
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Look at how the id column is defined. The Default column tells you whether anything generates a value.

    psql -X -d mydb -c '\d+ items'
  2. 2Match what you see, then jump to its fix

    1. 1\d shows id as integer, not null, with an empty Default column.The column is a plain integer with no defaultFix
    2. 2The same code works in another environment whose id column shows nextval(...) as default.The table was created without serial or identity (or a copy of it lost the default)Fix
    3. 3The column has a default, but the failing row still shows null in the id position.The application sends an explicit null for idFix

What does the error look like?

  • ERROR: null value in column "id" violates not-null constraint

Cause 1 of 3

The column is a plain integer with no default

1Check

If a column has no default, then the default is null. A primary key makes the column NOT NULL, so an insert that does not name id fails.

psql -X -d mydb -c '\d items'

2Fix

Turn the column into an identity column. This changes only later inserts; rows already stored are not changed.

ALTER TABLE items
  ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY;

3Verify

Insert a row without id and read it back.

INSERT INTO items (name) VALUES ('test') RETURNING id;

Confirmed by postgresql.org

Did this fix it?

Cause 2 of 3

The table was created without serial or identity (or a copy of it lost the default)

1Check

A serial column is shorthand for an integer column, a sequence, a NOT NULL constraint and a default of nextval on that sequence. If one of these parts is missing in this copy of the table, the default is missing too.

psql -X -d mydb -c '\d items'

2Fix

Rebuild the missing parts from the schema file that created the table, or declare the column the modern way in the migration.

CREATE TABLE items (
  id   integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  name text NOT NULL
);

3Verify

An insert that leaves out id succeeds.

INSERT INTO items (name) VALUES ('test') RETURNING id;

Confirmed by postgresql.org

Did this fix it?

Cause 3 of 3

The application sends an explicit null for id

1Check

A default is used only when the insert does not specify a value. The serial documentation says to leave the column out of the column list, or use the DEFAULT keyword. An explicit null is a value.

psql -X -d mydb -c "INSERT INTO items (id, name) VALUES (NULL, 'x');"

2Fix

Leave id out of the insert, or write DEFAULT in its place. In an ORM, do not map a null id into the insert statement.

INSERT INTO items (name) VALUES ('x');
INSERT INTO items (id, name) VALUES (DEFAULT, 'x');

3Verify

Both statements succeed and return new ids.

SELECT id, name FROM items ORDER BY id DESC LIMIT 2;

Confirmed by postgresql.org

Did this fix it?

How do I stop it coming back?

  • Use GENERATED ... AS IDENTITY for new primary keys.
  • Compare the \d output of production and test after schema copies.
  • Never fill id from application code unless you own the numbering.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.