NULL-input for concatenated string in generated column

Question

I have a table

CREATE TABLE IF NOT EXISTS club.climbers 
(
    climber_id SERIAL PRIMARY KEY,
    climber_first_name VARCHAR(20) NOT NULL,
    climber_last_name VARCHAR(30) NOT NULL,
    climber_full_name TEXT GENERATED ALWAYS AS (climber_first_name || ' ' || climber_last_name) STORED NOT NULL,
    sex_id INTEGER NOT NULL REFERENCES club.sex,
    climber_date_birth DATE NOT NULL,
    climber_phone VARCHAR(20) NOT NULL,
    postal_code_id INTEGER REFERENCES club.postal_codes,
    street VARCHAR(75) NOT NULL,
    building VARCHAR(5) NOT NULL,
    apartment VARCHAR(5),
    full_address TEXT GENERATED ALWAYS AS (street || ',' || building || '-' || apartment) STORED
);

But apartment can be NULL and then full_address would be NULL too. I need to ignore NULL values from apartment.

I tried to use CONCAT and COALESCE, but I don't know how to suppress a dangling '-' when apartment is NULL.

Answer 1

You can include the - in the first argument to coalesce() like:

full_address TEXT GENERATED ALWAYS AS (street || ',' || building || 
    coalesce('-' || apartment, '')) STORED);

Answer 2

One option is to use CASE expression:

full_address TEXT GENERATED ALWAYS AS 
    (street || ',' || case when apartment is not null then building || '-' || apartment 
                           else building end) STORED

which - for inserts as

insert into climbers (street, building, apartment) values ('Wall street', 'A', '10');
insert into climbers (street, building, apartment) values ('5th Avenue', 'B', null);

results in

street          building    apartment   full_address
Wall street     A           10          Wall street,A-10
5th Avenue      B           null        5th Avenue,B

See fiddle.

Answer 3

you can try this solution, I dont have any example to check comprehensively, but I believe it will work.

CREATE TABLE IF NOT EXISTS club.climbers
(
    climber_id SERIAL PRIMARY KEY,
    climber_first_name VARCHAR(20) NOT NULL,
    climber_last_name VARCHAR(30) NOT NULL,
    climber_full_name TEXT GENERATED ALWAYS AS (climber_first_name || ' ' || climber_last_name) STORED NOT NULL,
    sex_id INTEGER NOT NULL REFERENCES club.sex,
    climber_date_birth DATE NOT NULL,
    climber_phone VARCHAR(20) NOT NULL,
    postal_code_id INTEGER REFERENCES club.postal_codes,
    street VARCHAR(75) NOT NULL,
    building VARCHAR(5) NOT NULL,
    apartment VARCHAR(5),
    full_address TEXT GENERATED ALWAYS AS (
        street || ',' || building ||
        CASE WHEN apartment IS NOT NULL THEN '-' || apartment ELSE '' END
    ) STORED NOT NULL
);

Answer 4

For a single case with a single column, Andomar's version seems best. A custom function would be overkill.

For repeated use / multiple columns

Optimized expression, with white space and separators (only) where due:

immutable_concat_ws(' - ', street || ', ' || building, apartment)

immutable_concat_ws() is a custom function as explained below.
Basically:

concat_ws(' - ', street || ', ' || building, apartment)

See:

• How to concatenate columns in a Postgres SELECT?

But generated columns require IMMUTABLE expressions. See:

• Computed / calculated / virtual / derived / generated columns in PostgreSQL

So create the custom immutable_concat_ws() for text input only (guaranteed in your case). Ideally, as superuser:

CREATE OR REPLACE FUNCTION immutable_concat_ws(text, VARIADIC text[])
  RETURNS text
  LANGUAGE internal IMMUTABLE PARALLEL SAFE AS
'text_concat_ws';

See:

• Create an immutable clone of concat_ws

Or, without superuser privileges, and as "standard SQL" function (requires Postgres 14+). See:

• How to concatenate columns in a Postgres SELECT?

CREATE OR REPLACE FUNCTION immutable_concat_ws(text, VARIADIC text[])
  RETURNS text
  LANGUAGE sql IMMUTABLE PARALLEL SAFE
RETURN array_to_string($2, $1);