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

disho6za  于 12个月前  发布在  PostgreSQL
关注(0)|答案(4)|浏览(143)

我有一张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
);

字符串
但是apartment可以是NULL,那么full_address也会是NULL。我需要忽略来自apartment的NULL值。
我尝试使用CONCATCOALESCE,但我不知道如何在apartment为NULL时抑制悬空'-'。

sy5wg1nm

sy5wg1nm1#

您可以在coalesce()的第一个参数中包含-,如下所示:

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

字符串

xqk2d5yq

xqk2d5yq2#

一种选择是使用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

vm0i2vca

vm0i2vca3#

你可以尝试这个解决方案,我没有任何例子来全面检查,但我相信它会工作。

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

字符串

cqoc49vn

cqoc49vn4#

对于一个只有一列的情况,Andomar的版本似乎是最好的。自定义函数会大材小用。

重复使用/多列

优化的表达式,在适当的地方使用白色空格和分隔符(仅):

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

字符串
immutable_concat_ws()是一个自定义函数,如下所述。
基本上:

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


参见:

  • 如何在Postgres SELECT中连接列?
    生成的列需要**IMMUTABLE**表达式,请参见:
  • PostgreSQL中的计算/计算/虚拟/派生/生成列

因此,创建自定义immutable_concat_ws()仅用于文本输入(在您的情况下保证)。理想情况下,作为超级用户

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


参见:

  • 创建concat_ws的不可变克隆

或者,没有超级用户权限,并作为“标准SQL”函数(需要Postgres 14+).参见:

  • 如何在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);

相关问题