3

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.

0

4 Answers 4

2

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

full_address TEXT GENERATED ALWAYS AS (street || ',' || building || 
    coalesce('-' || apartment, '')) STORED);
Sign up to request clarification or add additional context in comments.

Comments

1

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.

Comments

1

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


Comments

0

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:

But generated columns require IMMUTABLE expressions. See:

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:

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

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.