postgresql 使用条件更新数组以处理NULL值

w6mmgewl 于 2023-03-01 发布在 PostgreSQL

关注(0)|答案(2)|浏览(237)

所以我有问题，我想过滤的东西，然后更新他们两次。
假设我有一个名为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_ids（即NULL）更新为空数组（如'{}'），因为此脚本

ARRAY(SELECT DISTINCT UNNEST(array_cat(competition_ids, ARRAY['hijk-23lm'] )))

只适用于空数组，而不是NULL。一旦我将它们转换为空数组，我想再次使用相同的脚本。
这个脚本只对空数组（'{}'）有效，对NULL无效。如果我能让这个脚本对NULL值有效，那么这篇文章就基本解决了。但是如果我不能，那么这篇文章仍然是个问题。
我使用的当前脚本没有将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';

postgresql

来源：https://stackoverflow.com/questions/75547215/updating-an-array-with-conditions-to-handle-null-values

2条答案

按热度按时间

whlutmcx1#

不需要取消嵌套和聚合数组。可以使用串联运算符||将新元素追加到数组中。要处理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'])

一个更好的解决方案可能是使用一个具有一对多关系的适当规范化的数据模型，其中您可以定义一个唯一的约束来确保这一点。

赞(0）回复(0）举报 2023-03-01

yxyvkwin2#

脚本的这一部分：

ARRAY(SELECT DISTINCT UNNEST(array_cat(competition_ids, ARRAY['hijk-23lm'])))

competition_ids字段中的null没有问题：online demo

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}

update语句不影响第一条记录的原因是null @> text[]会导致null，所以where条件会将其过滤掉，语句最终会跳过该行。
您可以使用coalesce()来修复它。

WHERE NOT coalesce(competition_ids,'{}'::text[]) @> ARRAY['hijk-23lm']

Online demo：

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）回复(0）举报 2023-03-01

我来回答

postgresql 使用条件更新数组以处理NULL值

2条答案

相关问题

热门标签

最新问答