Why am I getting a an error when creating a generated column in PostgreSQL?

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.

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:

• Combine two columns and add into one new column

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):

• Create an immutable clone of concat_ws

Aside: full_name needs to be varchar(101) (50+50+1) to make sense. Or just use text columns instead. See:

• Any downsides of using data type “text” for storing strings?

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:

• Computed / calculated / virtual / derived columns in PostgreSQL
• Store common query as column?