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.

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

Fix 1: Omit the generated column from the INSERT column list

When inserting into a table with GENERATED ALWAYS AS IDENTITY.

INSERT INTO orders (total) VALUES (100.00); -- id generated automatically

Fix 2: Use OVERRIDING SYSTEM VALUE if you must supply an identity value

During data migration where specific identity values must be preserved.

INSERT INTO orders (id, total) OVERRIDING SYSTEM VALUE
VALUES (999, 100.00);

Fix 3: Use GENERATED BY DEFAULT AS IDENTITY instead for more flexibility

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

ALTER TABLE orders ALTER COLUMN id
  SET GENERATED BY DEFAULT;

Version notes

Sources

Confidence assessment

See also