I have a PostgreSQL table that contains a column with array of strings, and I use SQLAlchemy to manage it.
I would like to build an “upsert” function, so if a specific cell contains as example {'a', 'b'}, and I try to update it with 'b', the output should be still {'a', 'b'}.
Is it possible?
2
Either way you have to de-duplicate merged values, consider writing your own plain sql function like this:
create function public.array_unique(a anyarray) returns anyarray
language sql
as
$$
select array (
select distinct v from unnest(a) as b(v)
)
$$;
then you can check the result
select public.array_unique(array ['a', 'b'] || array ['a'])
and then adapt it with
insert into distributors (did, dname)
values (5, '{a,b}'), (6, '{a}')
on conflict (did) do update set dname = public.array_unique(dname || excluded.dname);