EC
428C9
PostgreSQLERRORNotableSyntax Error or Access Rule ViolationHIGH confidence

generated always

What this means

SQLSTATE 428C9 is raised when an INSERT or UPDATE explicitly provides a value for a column defined as GENERATED ALWAYS AS IDENTITY or GENERATED ALWAYS AS (expression). These columns are managed entirely by Postgres and cannot be set by the user.

Why it happens
  1. 1INSERT provides a value for a GENERATED ALWAYS AS IDENTITY column
  2. 2UPDATE sets a value on a GENERATED ALWAYS computed column
How to reproduce

INSERT providing a value for a GENERATED ALWAYS identity column.

trigger — this will error
trigger — this will error
CREATE TABLE orders (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  total NUMERIC
);
INSERT INTO orders (id, total) VALUES (999, 100.00); -- 428C9

expected output

ERROR:  cannot insert a non-DEFAULT value into column "id"
DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.

Fix 1

Omit the generated column from the INSERT column list

WHEN When inserting into a table with GENERATED ALWAYS AS IDENTITY.

Omit the generated column from the INSERT column list
INSERT INTO orders (total) VALUES (100.00); -- id generated automatically

Why this works

Omitting the identity column from the INSERT list lets Postgres generate the value automatically.

Fix 2

Use OVERRIDING SYSTEM VALUE if you must supply an identity value

WHEN During data migration where specific identity values must be preserved.

Use OVERRIDING SYSTEM VALUE if you must supply an identity value
INSERT INTO orders (id, total) OVERRIDING SYSTEM VALUE
VALUES (999, 100.00);

Why this works

OVERRIDING SYSTEM VALUE explicitly bypasses the GENERATED ALWAYS restriction, permitting a user-supplied value. Use only for data migration.

Fix 3

Use GENERATED BY DEFAULT AS IDENTITY instead for more flexibility

WHEN When the column should allow user-supplied values in some cases.

Use GENERATED BY DEFAULT AS IDENTITY instead for more flexibility
ALTER TABLE orders ALTER COLUMN id
  SET GENERATED BY DEFAULT;

Why this works

BY DEFAULT allows user-supplied values without requiring OVERRIDING SYSTEM VALUE.

Version notes
Postgres 10+

GENERATED AS IDENTITY introduced in Postgres 10. GENERATED ALWAYS AS (expression) computed columns added in Postgres 12.

Sources
Official documentation ↗

Class 42 — Syntax Error or Access Rule Violation

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All PostgreSQL errors