Updating an array that is NULL to an empty array

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

Say I have this table called 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}

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

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.

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

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

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

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

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;

Then use it like this:

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

This part of your script:

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

has no problem with a null in the competition_ids field: 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}

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.

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