Search code examples
sqlpostgresqldatabase-designstring-concatenationcalculated-columns

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.

Solution

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