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 because of it full_adress can be NULL too, but I need only ignore NULL value from apartment
I tried to use CONCAT and COALESCE, but I don’t know how to ignore ‘-‘ before apartment too
3
Answers
You can include the
-
in the first argument to coalesce() like:you can try this solution, I dont have any example to check comprehensively, but I believe it will work.
One option is to use
CASE
expression:which – for inserts as
results in
See fiddle.