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

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?

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.