将一个为NULL的数组更新为空数组。

huangapple
go评论90阅读模式
英文:

Updating an array that is NULL to an empty array

问题

以下是翻译好的代码部分：

  1. So I have problem where I want to filter things and then update them twice.
  3. Say I have this table called `licence`:
  5. licence_id | user_id |  property   |     validity_dates             |  competition_ids    |
  6. -----------+---------+-------------+--------------------------------+-----------------------
  7.          1 |      20 | JOHN        | [2022-01-01,2025-01-02)        | 
  8.          2 |      21 | JOHN        | [2022-01-01,2025-01-02)        | {abcd-efg, asda-12df}
  10. I'd like to update `competition_ids` which are `NULL` into an empty array such as `{}` because this script
  12. ARRAY(SELECT DISTINCT UNNEST(array_cat(competition_ids, ARRAY['hijk-23lm' ])))
  13. works only on empty arrays instead of `NULL`s. Once I convert them into empty arrays, I'd like to use the same script again.
  15. The script only works on empty arrays ( '{}') and not `NULL`s. If I can make the script work with `NULL` values, then this post is pretty much solved. But If I can't then this post is still a problem unfortunately.
  17. The current script I'm using does not change `NULLS` into '{} ' and I'm not sure exactly why.
  19. UPDATE licence 
  20. SET competition_ids =
  21.   (CASE
  22.     WHEN competition_ids is NULL 
  23.     THEN ARRAY['{}' ]
  24.     ELSE ARRAY(SELECT DISTINCT UNNEST(array_cat(competition_ids, ARRAY['hijk-23lm' ])))
  25.   END)
  26. WHERE NOT competition_ids  @> ARRAY['hijk-23lm' ]
  27. AND validity_dates = DATERANGE('2022-01-01', '2025-01-02', '[)')
  28. AND property = 'JOHN';
英文:

So I have problem where I want to filter things and then update them twice.

Say I have this table called licence:

  1. licence_id | user_id |  property   |     validity_dates             |  competition_ids    |
  2. -----------+---------+-------------+--------------------------------+-----------------------
  3.          1 |      20 | JOHN        | [2022-01-01,2025-01-02)        | 
  4.          2 |      21 | JOHN        | [2022-01-01,2025-01-02)        | {abcd-efg, asda-12df}

I'd like to update competition_ids which are NULL into an empty array such as '{}' because this script

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

works only on empty arrays instead of NULLs. Once I convert them into empty arrays, I'd like to use the same script again.

The script only works on empty arrays ( '{}' ) and not NULLs. If I can make the script work with NULL values, then this post is pretty much solved. But If I can't then this post is still a problem unfortunately.

The current script I'm using does not change NULLS into '{}' and I'm not sure exactly why.

  1. UPDATE licence 
  2. SET competition_ids =
  3.   (CASE
  4.     WHEN competition_ids is NULL 
  5.     THEN ARRAY['{}'] THEN ARRAY(SELECT DISTINCT UNNEST(array_cat(competition_ids, ARRAY['hijk-23lm'] )))
  6.     ELSE ARRAY(SELECT DISTINCT UNNEST(array_cat(competition_ids, ARRAY['hijk-23lm'] )))
  7.   END)
  8. WHERE NOT competition_ids  @> ARRAY['hijk-23lm']
  9. AND validity_dates = DATERANGE('2022-01-01', '2025-01-02', '[)')
  10. AND property = 'JOHN';

答案1

得分: 1

不需要展开和聚合数组。您可以使用连接运算符 || 将新元素附加到数组。要处理 NULL 值，请使用 coalesce()

  1. UPDATE licence 
  2.    SET competition_ids = coalesce(competition_ids, '{}' )||'hijk-23lm'
  3. WHERE NOT competition_ids @> ARRAY['hijk-23lm']
  4. AND validity_dates = DATERANGE('2022-01-01', '2025-01-02', '[)' )
  5. AND property = 'JOHN';

如果您处理可能包含唯一元素的数组，最好的方法可能是创建一个处理这种情况的函数：

  1. create function append_unique(p_one text[], p_other text[])
  2.   returns text[]
  3. as
  4. $$
  5.   select array(
  6.      select *
  7.      from unnest(coalesce(p_one, '{}' )) 
  8.      union 
  9.      select *
  10.      from unnest(coalesce(p_other, '{}' ))
  11.    );
  12. $$   
  13. language sql
  14. immutable;

然后像这样使用它：

  1. SET competition_ids = append_unique(competition_ids, ARRAY['hijk-23lm'])

一个更好的解决方案可能是使用一个经过适当规范化的数据模型，其中可以定义一个确保唯一性的唯一约束条件。然后可以使用 insert ... on conflict 来添加元素。

英文:

There is no need to unnest and aggregate the array. You can use the concatenation operator || to append a new element to an array. To deal with NULL values use coalesce()

  1. UPDATE licence 
  2.    SET competition_ids = coalesce(competition_ids, '{}')||'hijk-23lm'
  3. WHERE NOT competition_ids  @> ARRAY['hijk-23lm']
  4. AND validity_dates = DATERANGE('2022-01-01', '2025-01-02', '[)')
  5. AND property = 'JOHN';

If you are dealing with arrays that possibly contain unique elements, the best approach is probably to create a function that handles this:

  1. create function append_unique(p_one text[], p_other text[])
  2.   returns text[]
  3. as
  4. $$
  5.   select array(
  6.      select *
  7.      from unnest(coalesce(p_one, '{}')) 
  8.      union 
  9.      select *
  10.      from unnest(coalesce(p_other, '{}'))
  11.    );
  12. $$   
  13. language sql
  14. immutable;

Then use it like this:

  1. SET competition_ids = append_unique(competition_ids, ARRAY['hijk-23lm'])

A better solution might be to use a properly normalized data model with a one-to-many relationship where you can define a unique constraint that ensures this. Adding elements can then be done using insert ... on conflict

答案2

得分: 1

以下是代码部分的中文翻译：

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

没有问题，即使competition_ids字段中包含null值：在线演示

  1. select ARRAY(SELECT DISTINCT UNNEST(array_cat(null, ARRAY['hijk-23lm'])));
  2. -- {hijk-23lm}
  3. select ARRAY(SELECT DISTINCT UNNEST(array_cat('{}', ARRAY['hijk-23lm'])));
  4. -- {hijk-23lm}

你的update语句之所以不影响第一行记录是因为null @> text[]会产生null，所以where条件将其过滤掉。该语句最终跳过了该行。

你可以使用 coalesce() 来修复它。

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

在线演示:

  1. UPDATE licence 
  2. SET competition_ids 
  3. = ARRAY(SELECT DISTINCT UNNEST(array_cat(competition_ids, ARRAY['hijk-23lm']))
  4. WHERE NOT coalesce(competition_ids,'{}'::text[]) @> ARRAY['hijk-23lm']
  5. AND validity_dates = DATERANGE('2022-01-01', '2025-01-02', '[)')
  6. AND property = 'JOHN';
英文:

This part of your script:

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

has no problem with a null in the competition_ids field: online demo

  1. select ARRAY(SELECT DISTINCT UNNEST(array_cat(null, ARRAY['hijk-23lm'])));
  2. -- {hijk-23lm}
  3. select ARRAY(SELECT DISTINCT UNNEST(array_cat('{}', ARRAY['hijk-23lm'])));
  4. -- {hijk-23lm}

The reason your update statement doesn't affect the first record is because null @> text[] results in a null so your where condition filters it out. The statement ends up skipping that row.

You can use coalesce() to fix it.

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

Online demo:

  1. UPDATE licence 
  2. SET competition_ids 
  3. = ARRAY(SELECT DISTINCT UNNEST(array_cat(competition_ids, ARRAY['hijk-23lm'])))
  4. WHERE NOT coalesce(competition_ids,'{}'::text[]) @> ARRAY['hijk-23lm']
  5. AND validity_dates = DATERANGE('2022-01-01', '2025-01-02', '[)')
  6. AND property = 'JOHN';

huangapple
  • 本文由 发表于 2023年2月23日 23:52:28
  • 转载请务必保留本文链接：https://go.coder-hub.com/75547215.html
  • arrays
  • null
  • postgresql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定