连接字符串，而不是仅替换它

Question

我有一个带有标准列的表，我想在其中执行常规INSERTs。

但是其中一列是具有特殊语义的varchar类型。它是一个字符串，应该表现为一组字符串，其中该组的元素用逗号分隔。

例如。如果一行在varchar列中有值fish,sheep,dove，并且我插入字符串,fish,eagle，我希望结果是fish,sheep,dove,eagle(即eagle被添加到集合中，但fish没有，因为它已经在集合中了(。

我这里有这个CCD_ 9代码；集合串联"；我想要的：

SELECT string_agg(unnest, ',') AS x FROM (SELECT DISTINCT unnest(string_to_array('fish,sheep,dove' || ',fish,eagle', ','))) AS x;

但我不知道如何将这种逻辑应用于插入。

我想要的是：

CREATE TABLE IF NOT EXISTS t00(
userid int8 PRIMARY KEY,
a      int8,
b      varchar);
INSERT  INTO t00 (userid,a,b)  VALUES (0,1,'fish,sheep,dove');
INSERT  INTO t00 (userid,a,b)  VALUES (0,1,',fish,eagle')
ON CONFLICT (userid)
DO UPDATE SET
a = EXCLUDED.a,
b = SELECT string_agg(unnest, ',') AS x FROM (SELECT DISTINCT unnest(string_to_array(t00.b || EXCLUDED.b, ','))) AS x;

我怎样才能取得这样的成就？

Answer 1

存储逗号分隔的值一开始就是一个巨大的错误。但是，如果你真的想让你的生活比需要的更艰难，你可能想创建一个合并两个逗号分隔列表的函数：

create function merge_lists(p_one text, p_two text)
returns text
as
$$
select string_agg(item, ',')
from (
select e.item
from unnest(string_to_array(p_one, ',')) as e(item)
where e.item <> '' --< necessary because of the leading , in your data
union 
select t.item
from unnest(string_to_array(p_two, ',')) t(item)
where t.item <> ''
) t;
$$
language sql;  

如果您使用的是Postgres 14或更高版本，unnest(string_to_array(..., ','))可以替换为string_to_table(..., ',')

然后您的INSERT语句变得简单一点：

INSERT  INTO t00 (userid,a,b)  VALUES (0,1,',fish,eagle')
ON CONFLICT (userid)
DO UPDATE SET
a = EXCLUDED.a,
b = merge_lists(excluded.b, t00.b);

Answer 2

我想我只是缺少了SELECT语句周围的括号：

INSERT  INTO t00 (userid,a,b)  VALUES (0,1,',fish,eagle')
ON CONFLICT (userid)
DO UPDATE SET
a = EXCLUDED.a,
b = (SELECT string_agg(unnest, ',') AS x FROM (SELECT DISTINCT unnest(string_to_array(t00.b || EXCLUDED.b, ','))) AS x);