PostgreSQL生成列NULL处理：消除full_address冗余短横线

针对你创建的club.climbers表，当apartment字段为NULL时，full_address生成列会出现NULL或多余短横线的问题，可以通过以下两种方式解决：

方法1：使用CASE条件判断 #

直接通过CASE语句判断apartment是否非空，仅当存在值时才拼接短横线和公寓号：

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

方法2：使用COALESCE简化拼接 #

利用COALESCE函数处理NULL场景，当apartment为NULL时，将'-' || apartment转换为空字符串，避免多余短横线：

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 || COALESCE('-' || apartment, '')
    ) STORED
);

两种方法的效果一致：

• 当apartment有值时，生成类似"Main St,123-45"的地址
• 当apartment为NULL时，生成类似"Main St,123"的地址，不会出现多余的短横线或NULL值

内容的提问来源于stack exchange，提问作者Юра Ковалeв