Sql NULL生成的列中连接的字符串的输入

发布于12月31日

我有一张桌子

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

但是apartment可以是空的，那么full_address也是空的.我需要忽略从apartment开始的空值.

我试着用CONCAT和COALESCE，但我不知道当apartment为空时如何 suppress 悬挂的‘-’.

推荐答案

一种 Select 是使用CASE表达式:

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

其中-对于插入为

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

在.中的结果

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

请参见fiddle.