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

Question

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.

Cress

Answer 1

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:

• 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 concatenation logic.

Related:

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

Reichsmark

Answer 2

This works with the || operator:

CREATE TABLE person (
    person_id smallserial NOT NULL,
    first_name character varying(50),
    last_name character varying(50),
    full_name character varying(100) generated always as (first_name || ' ' || last_name) STORED,
    birth_date date,
    created_timestamp timestamp default current_timestamp,
    PRIMARY KEY (person_id)
);

I am not sure of the technical reasons why concat() is considered mutable, but || is not.

If you want to handle NULL values in the columns, then it is a little more complicated. I might recommend:

trim(both ' ' from
     (' ' || coalesce(first_name, '') || ' ' || coalesce(last_name, '')
     )
    )

Of course, this isn't exactly the same as your expression, because it removes spaces from the beginning and end of the names.

Azikiwe

Yes it doesn't make sense. Thank you! – Cress

The replacement is not equivalent. – Reichsmark