提问人：Panface 提问时间：2/23/2023 最后编辑：Panface 更新时间：3/2/2023 访问量：929
将 NULL 数组更新为空数组
Updating an array that is NULL to an empty array
问：
所以我有问题，我想过滤东西，然后更新它们两次。
假设我有这个表叫做：licence
licence_id | user_id | property | validity_dates | competition_ids |
-----------+---------+-------------+--------------------------------+-----------------------
1 | 20 | JOHN | [2022-01-01,2025-01-02) |
2 | 21 | JOHN | [2022-01-01,2025-01-02) | {abcd-efg, asda-12df}
我想将哪些更新为空数组，例如因为这个脚本competition_idsNULL'{}'
ARRAY(SELECT DISTINCT UNNEST(array_cat(competition_ids, ARRAY['hijk-23lm'] )))
仅适用于空数组而不是 s。一旦我将它们转换为空数组，我想再次使用相同的脚本。NULL
该脚本仅适用于空数组 （ ） 而不是 s。如果我能使脚本使用值，那么这篇文章就解决了。但是，如果我做不到，那么不幸的是，这篇文章仍然是一个问题。'{}'NULLNULL
我当前使用的脚本没有更改为“{}”，我不确定确切的原因。NULLS
UPDATE licence
SET competition_ids =
(CASE
WHEN competition_ids is NULL
THEN ARRAY['{}'] THEN ARRAY(SELECT DISTINCT UNNEST(array_cat(competition_ids, ARRAY['hijk-23lm'] )))
ELSE ARRAY(SELECT DISTINCT UNNEST(array_cat(competition_ids, ARRAY['hijk-23lm'] )))
END)
WHERE NOT competition_ids @> ARRAY['hijk-23lm']
AND validity_dates = DATERANGE('2022-01-01', '2025-01-02', '[)')
AND property = 'JOHN';
答：
1赞
user330315
2/24/2023
#1
无需取消嵌套和聚合数组。您可以使用串联运算符将新元素追加到数组中。若要处理 NULL 值，请使用||coalesce()
UPDATE licence
SET competition_ids = coalesce(competition_ids, '{}')||'hijk-23lm'
WHERE NOT competition_ids @> ARRAY['hijk-23lm']
AND validity_dates = DATERANGE('2022-01-01', '2025-01-02', '[)')
AND property = 'JOHN';
如果你正在处理可能包含唯一元素的数组，最好的方法可能是创建一个处理这个问题的函数：
create function append_unique(p_one text[], p_other text[])
returns text[]
as
$$
select array(
select *
from unnest(coalesce(p_one, '{}'))
union
select *
from unnest(coalesce(p_other, '{}'))
);
$$
language sql
immutable;
然后像这样使用它：
SET competition_ids = append_unique(competition_ids, ARRAY['hijk-23lm'])
更好的解决方案可能是使用具有一对多关系的正确规范化数据模型，您可以在其中定义唯一约束来确保这一点。然后可以使用以下命令添加元素insert ... on conflict
评论
0赞
Panface
2/24/2023
我使用了 unnest 和 aggregate 来避免重复，尽管我认为鉴于您的解决方案，我可能会找到一种方法来实现它。
1赞
2/24/2023
正如您在 WHERE 子句中使用的一样，您不会将字符串添加到已包含该字符串的数组中。NOT competition_ids @> ARRAY['hijk-23lm']
0赞
Zegarek
2/24/2023
如果因为可以提供多个字符串作为此脚本的输入而选择此脚本，则输入中的两个相同的字符串可能会导致插入重复项。不过，仅从输入中删除重复项并将其连接起来，而不是对输入的串联和先前删除重复数据的现有重复数据进行重复数据删除会更有意义。array_cat()array_append()
0赞
2/24/2023
@Zegarek：如果在数组中保留唯一元素很重要，那么编写自定义函数将使 UPDATE 语句更容易。
0赞
Panface
2/24/2023
没错，我正在添加多个数组元素，因此改用 array_cat。输入是由外部方进行的，有时现有用户已经有几个competition_ids。
1赞
Zegarek
2/24/2023
#2
脚本的这一部分：
ARRAY(SELECT DISTINCT UNNEST(array_cat(competition_ids, ARRAY['hijk-23lm'])))
在现场没有问题：在线演示nullcompetition_ids
select ARRAY(SELECT DISTINCT UNNEST(array_cat(null, ARRAY['hijk-23lm'])));
-- {hijk-23lm}
select ARRAY(SELECT DISTINCT UNNEST(array_cat('{}', ARRAY['hijk-23lm'])));
-- {hijk-23lm}
您的语句不影响第一条记录的原因是，您的条件会将其过滤掉。该语句最终会跳过该行。updatenull @> text[]nullwhere
你可以使用 coalesce（） 来修复它。
WHERE NOT coalesce(competition_ids,'{}'::text[]) @> ARRAY['hijk-23lm']
在线演示：
UPDATE licence
SET competition_ids
= ARRAY(SELECT DISTINCT UNNEST(array_cat(competition_ids, ARRAY['hijk-23lm'])))
WHERE NOT coalesce(competition_ids,'{}'::text[]) @> ARRAY['hijk-23lm']
AND validity_dates = DATERANGE('2022-01-01', '2025-01-02', '[)')
AND property = 'JOHN';
评论
0赞
Panface
2/24/2023
我想知道在这种情况下 Coalesce 内部如何处理它？它返回第一个不是 NULL 的参数？但是，对于 competition_ids 为 NULL 的情况，它如何返回competition_ids？
0赞
Zegarek
2/24/2023
您的猜测正是您在链接文档中找到的内容：“COALESCE 函数返回其第一个不为 null 的参数。您只需要在本节中使用它，因为在您的部分中，它自己处理 null 参数。观看此演示whereSETarray_cat()
上一个：如何在 Java 中比较字符串？
下一个：强制开机自检动态选择，包括空值
评论