Why am I getting a an error when creating a generated column in PostgreSQL?
Feb 16
CREATE TABLE my_app.person
(
person_id smallserial NOT NULL,
first_name character varying(50),
last_name character varying(50),
full_name character varying(100) generated always as (concat(first_name, ' ', last_name)) STORED,
birth_date date,
created_timestamp timestamp default current_timestamp,
PRIMARY KEY (person_id)
);
Error: generation expression is not immutable
The goal is to populate the first name and last into the full name column.
postgresql1 answer
Accepted answer · original discussion
Feb 16
The concat() function is not IMMUTABLE (only STABLE) because it can invoke datatype output functions (like timestamptz_out) that depend on locale settings. Tom Lane (core developer) explains it here.
And first_name || ' ' || last_name is not equivalent to concat(first_name, ' ', last_name) while at least one column can be NULL.
Detailed explanation:
Solution
To make it work, exactly the way you demonstrated:
CREATE TABLE person (
person_id smallserial PRIMARY KEY
, first_name varchar(50)
, last_name varchar(50)
, full_name varchar(101) GENERATED ALWAYS AS
(CASE WHEN first_name IS NULL THEN last_name
WHEN last_name IS NULL THEN first_name
ELSE first_name || ' ' || last_name END) STORED
, ...
);
db<>fiddle here
The CASE expression is as fast as it gets - substantially faster than multiple concatenation and function calls. And exactly correct.
Or, if you know what you are doing and have the necessary privileges, create an IMMUTABLE concat function as demonstrated here (to replace the CASE expression):
Aside: full_name needs to be varchar(101) (50+50+1) to make sense. Or just use text columns instead. See:
General Advice
The best solution depends on how you plan to deal with NULL values (and empty strings) exactly. I would probably not add a generated column. That's typically more expensive and error prone overall than to concatenate the full name on the fly. Consider a view, or a function encapsulating the concatenation logic.
Related:
0 question comments
Use comments to ask for clarification. Post a solution as an answer.
No question comments on this page.