The PostgreSQL error "428C9: generated_always" occurs when attempting to insert or update a value in a GENERATED ALWAYS column, which violates the column's generation constraint. This error indicates that you're trying to manually modify a column that PostgreSQL automatically manages based on other column values.
The error code 428C9 in PostgreSQL is a constraint violation error specifically related to GENERATED ALWAYS columns. In PostgreSQL 12 and later, generated columns are virtual columns whose values are automatically computed from other columns in the same table using an expression. When a column is defined as GENERATED ALWAYS, PostgreSQL enforces that the column value can only be generated by the database system, not by user-provided values during INSERT or UPDATE operations. This error occurs when you attempt to explicitly provide a value for such a column, either directly in an INSERT statement or through an UPDATE that tries to modify the generated column. Generated columns are useful for storing computed values that should always be consistent with other data in the row, such as calculated fields, transformed data, or derived metrics. The GENERATED ALWAYS constraint ensures data integrity by preventing manual modifications that could create inconsistencies.
If you're getting this error during an INSERT operation, remove the GENERATED ALWAYS column from your column list. PostgreSQL will automatically compute the value for generated columns.
Example of incorrect INSERT:
-- This will fail for a table with GENERATED ALWAYS column 'full_name'
INSERT INTO users (first_name, last_name, full_name)
VALUES ('John', 'Doe', 'John Doe');Correct approach:
-- Omit the generated column - PostgreSQL will compute it automatically
INSERT INTO users (first_name, last_name)
VALUES ('John', 'Doe');The database will automatically compute the full_name based on the generation expression defined in the table schema.
Never include GENERATED ALWAYS columns in UPDATE statements. These columns can only be modified by changing the source columns they depend on.
Example of incorrect UPDATE:
-- This will fail for a table with GENERATED ALWAYS column 'total_price'
UPDATE orders
SET quantity = 2, total_price = 100.00
WHERE order_id = 123;Correct approach:
-- Only update the source columns, not the generated column
UPDATE orders
SET quantity = 2
WHERE order_id = 123;The total_price will be automatically recalculated based on the new quantity and the unit_price from the row.
If you're using an ORM like Prisma, Sequelize, or TypeORM, configure it to exclude generated columns from INSERT and UPDATE operations.
For Prisma, you can mark the field as read-only in your schema:
model User {
id Int @id @default(autoincrement())
firstName String
lastName String
fullName String @generated(expression: "concat(first_name, ' ', last_name)")
}For TypeORM, use the @Generated decorator:
import { Entity, Column, Generated } from 'typeorm';
@Entity()
export class User {
@Column()
firstName: string;
@Column()
lastName: string;
@Column()
@Generated('always')
fullName: string;
}Check your framework's documentation for handling generated columns properly.
If you need to be able to provide values for the column during INSERT (but not UPDATE), consider changing the column definition from GENERATED ALWAYS to GENERATED BY DEFAULT.
Current problematic definition:
CREATE TABLE products (
id SERIAL PRIMARY KEY,
price NUMERIC(10,2),
tax_rate NUMERIC(5,4),
total_price NUMERIC(10,2) GENERATED ALWAYS AS (price * (1 + tax_rate)) STORED
);Alternative definition:
CREATE TABLE products (
id SERIAL PRIMARY KEY,
price NUMERIC(10,2),
tax_rate NUMERIC(5,4),
total_price NUMERIC(10,2) GENERATED BY DEFAULT AS (price * (1 + tax_rate)) STORED
);With GENERATED BY DEFAULT, you can provide a value during INSERT, and if you omit it, PostgreSQL will generate the value. However, you still cannot UPDATE a generated column directly.
Note: This changes the column behavior significantly, so only use this if you have a legitimate need to override the generated value during INSERT.
When migrating data or performing bulk imports, exclude GENERATED ALWAYS columns from your source data or transformation scripts.
Using COPY command:
-- Create a temporary table without the generated column
CREATE TEMP TABLE users_temp (LIKE users);
ALTER TABLE users_temp DROP COLUMN full_name;
-- Copy data without the generated column
COPY users_temp (first_name, last_name) FROM '/path/to/data.csv' CSV HEADER;
-- Insert into the real table (generated column will be computed automatically)
INSERT INTO users (first_name, last_name)
SELECT first_name, last_name FROM users_temp;Using INSERT with SELECT:
-- Correct: Select only source columns, not generated columns
INSERT INTO new_table (col1, col2, col3) -- col4 is GENERATED ALWAYS
SELECT col1, col2, col3 FROM old_table;
-- Incorrect: Includes the generated column
INSERT INTO new_table (col1, col2, col3, col4) -- col4 is GENERATED ALWAYS
SELECT col1, col2, col3, col4 FROM old_table; -- This will failCheck your table definition to understand which columns are GENERATED ALWAYS:
-- View column definitions including generation expressions
SELECT
column_name,
is_generated,
generation_expression,
data_type
FROM information_schema.columns
WHERE table_name = 'your_table_name'
ORDER BY ordinal_position;
-- Or using \d+ in psql
\d+ your_table_nameUnderstand the generation expression to know which source columns affect the generated value. For example, if you have:
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
quantity INTEGER,
unit_price NUMERIC(10,2),
total_price NUMERIC(10,2) GENERATED ALWAYS AS (quantity * unit_price) STORED
);To change the total_price, you must update either quantity or unit_price, not total_price directly.
PostgreSQL supports two types of generated columns: STORED and VIRTUAL (though VIRTUAL is not yet implemented as of PostgreSQL 16). STORED generated columns are physically stored on disk and can be indexed, while their values are computed when rows are inserted or updated.
Key differences between GENERATED ALWAYS and GENERATED BY DEFAULT:
- GENERATED ALWAYS: The column value is always generated by PostgreSQL. You cannot provide a value during INSERT or UPDATE.
- GENERATED BY DEFAULT: You can provide a value during INSERT, but if you omit it, PostgreSQL generates the value. You still cannot UPDATE the column directly.
Performance considerations:
- STORED generated columns add storage overhead but can be indexed for faster queries
- The generation expression is evaluated during INSERT and UPDATE operations
- Complex expressions or expressions involving multiple table scans can impact performance
Common use cases for generated columns:
- Computed fields (total price, age from birth date, etc.)
- Data normalization (uppercase names, trimmed strings)
- Derived metrics (BMI from height and weight)
- Concatenated fields (full name from first and last name)
When designing schemas with generated columns, ensure that the generation expression doesn't create circular dependencies or reference other generated columns in a way that creates dependency loops.
ERROR: syntax error at end of input
Syntax error at end of input in PostgreSQL
Bind message supplies N parameters but prepared statement requires M
Bind message supplies N parameters but prepared statement requires M in PostgreSQL
Multidimensional arrays must have sub-arrays with matching dimensions
Multidimensional arrays must have sub-arrays with matching dimensions
ERROR: value too long for type character varying
Value too long for type character varying
insufficient columns in unique constraint for partition key
How to fix "insufficient columns in unique constraint for partition key" in PostgreSQL