ERROR: cannot insert a non-DEFAULT value into column "id" (SQLSTATE 428C9) means you supplied a value for an identity column declared GENERATED ALWAYS, which only the database may fill. Leave the column out of the INSERT. If you really must keep the ids, for a data copy, add OVERRIDING SYSTEM VALUE as the hint says.

For ordinary inserts, drop id from the column list and its value from VALUES. For a one-off copy with fixed ids, use the override and then move the identity sequence past the highest id, or the next normal insert can collide.

The error

INSERT INTO products (id, name) VALUES (100, 'Desk lamp');
psql:/tmp/q2.sql:6: ERROR:  cannot insert a non-DEFAULT value into column "id"
DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.

Why it happens

id integer GENERATED ALWAYS AS IDENTITY tells PostgreSQL that it alone assigns ids, from a sequence attached to the column. An explicit value would bypass the sequence, so PostgreSQL refuses it unless you say on purpose that you are overriding it. The DETAIL and HINT lines give the whole story.

The other flavour, GENERATED BY DEFAULT AS IDENTITY, accepts an explicit value without complaint: the same INSERT ... VALUES (100, 'Desk lamp') on such a table returned INSERT 0 1. So the error appears when a script or seed written for a BY DEFAULT table runs against an ALWAYS one, or when application code sends an id of its own.

The fix

INSERT INTO products (name) VALUES ('Desk lamp');
INSERT 0 1

For a copy that must keep its ids, override, then reset the sequence:

INSERT INTO products (id, name) OVERRIDING SYSTEM VALUE VALUES (100, 'Desk chair');
INSERT 0 1
SELECT id, name FROM products ORDER BY id;
 id  |    name
-----+------------
   1 | Desk lamp
 100 | Desk chair
(2 rows)

SELECT setval(pg_get_serial_sequence('products', 'id'), (SELECT max(id) FROM products));
 setval
--------
    100
(1 row)

INSERT INTO products (name) VALUES ('Bookshelf');
INSERT 0 1
SELECT id, name FROM products ORDER BY id;
 id  |    name
-----+------------
   1 | Desk lamp
 100 | Desk chair
 101 | Bookshelf
(3 rows)

After the setval, the next generated id was 101, right after the copied row. Without it, the override does not move the sequence: on a test table where the override inserted id 2, the next two normal inserts got id 1 and then failed with duplicate key value violates unique constraint "products3_pkey", Key (id)=(2) already exists. From .NET, Npgsql reports this as a PostgresException with SqlState 428C9.

How it was reproduced

A postgres:18 container (PostgreSQL 18.4, Debian build 18.4-1.pgdg13+1) on Docker 29.9.2, with products (id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name text NOT NULL). The explicit id failed, the insert without it gave id 1, the override inserted id 100, and after setval the next insert got 101. A second table declared GENERATED BY DEFAULT accepted the explicit id. A verbose run printed ERROR: 428C9.

Frequently asked

How do I insert an explicit id into a GENERATED ALWAYS identity column?
Write INSERT INTO table (id, ...) OVERRIDING SYSTEM VALUE VALUES (...). Afterwards run setval on pg_get_serial_sequence('table', 'id') with the highest id so later inserts do not reuse it.
What is the difference between GENERATED ALWAYS and GENERATED BY DEFAULT?
GENERATED ALWAYS refuses an explicit value unless you add OVERRIDING SYSTEM VALUE. GENERATED BY DEFAULT uses the sequence only when you leave the column out and accepts an explicit value.
Why does my next insert collide after copying rows with fixed ids?
Copying ids with OVERRIDING SYSTEM VALUE does not move the identity sequence. Set it past the highest id with setval, as shown in the post, before normal inserts continue.

More decoded errors in the Fixes category.