INSERT fails because the id column receives null
ERROR: null value in column "id" violates not-null constraintShort 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.
Fix it fast
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'2Match what you see, then jump to its fix
- 1
\d shows id as integer, not null, with an empty Default column.The column is a plain integer with no defaultFix - 2
The 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
The column has a default, but the failing row still shows null in the id position.The application sends an explicit null for idFix
- 1
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
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
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
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
Databases
Adding a NOT NULL column to a table that already has rows
ERROR: column "zipcode" of relation "distributors" contains null values
ADD COLUMN ..
3 causes, each with a fixDatabases
DBeaver cannot run pg_restore: the utility is not found in the client home
Utility 'pg_restore' not found in client home '/usr/lib' (/usr/lib)
DBeaver runs pg_restore from a local PostgreSQL client folder; it does not bundle one.
3 causes, each with a fixDatabases
DBeaver cannot download the PostgreSQL driver from Maven
The PostgreSQL driver files are missing and the download does not complete
DBeaver downloads the PostgreSQL JDBC driver from a Maven repository the first time you connect.
3 causes, each with a fixDatabases
python manage.py migrate finishes but the tables for my app are missing
relation "myapp_book" does not exist
migrate only applies migration files that exist.
3 causes, each with a fixDatabases
Django save fails on PostgreSQL: null value in a column violates a not-null constraint
django.db.utils.IntegrityError: null value in column "price" of relation "shop_product" violates not-null constraint
A model field is NOT NULL in the database unless it has null=True.
3 causes, each with a fixDatabases
Django migration on PostgreSQL stops with 'pending trigger events'
django.db.utils.OperationalError: cannot ALTER TABLE "mytable" because it has pending trigger events
PostgreSQL runs a Django migration in one transaction.
2 causes, each with a fix