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.
Advertisement
Answer
concat() is a 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 exact concatenation logic.
Related: