Upsert on an array column

I am trying to do an upsert on the array column but I am not able to achieve the following result.

Table name settings

id primary key unique
user_id foreign_key integer unique
friends array
tags array
count integer
created_at datetime
updated_at datetime

select * from settings order by id asc limit 1;

id                         | 177 
user_id                    | 111
friends                    | {1,2,3}
tags                       | {4,5}
created_at                 | 2020-01-23 10:30:27.814489
updated_at                 | 2020-02-29 00:00:00
count                      | 5

 insert into settings(
   user_id, friends, tags, created_at, updated_at)
 VALUES (111, '{6,7}', '{5000,5001}', now()::date , now()::date )
 on conflict (user_id) do 
 update set
   friends = array_cat(excluded.friends, '{6,7}'),
   tags = array_cat(excluded.tags, '{5000,5001}'),
   updated_at = now()::date;

After running the above query when I check the result

select * from settings order by id asc limit 1;

id                         | 177 
user_id                    | 111
friends                    | {6,7,6,7}
tags                       | {5000,5001,5000,5001}
created_at                 | 2020-01-23 10:30:27.814489
updated_at                 | 2020-02-29 00:00:00
count                      | 5

I am losing my old values. Also, the new values are getting inserted twice. Can anyone help me with this

Note: array_append won’t work because it appends only one item at a time whereas I need to append an array

Answer

You can use || to append one array to another:

insert into settings
  (user_id, friends, tags, created_at, updated_at)
VALUES 
  (111, '{6,7}', '{5000,5001}', current_timestamp, current_timestamp)
on conflict (user_id) do 
 update set
   friends = settings.friends || excluded.friends,
   tags = settings.tags || excluded.tags,
   updated_at = current_timestamp;

Advertisement

Answer