Postgres adding unexisting varchar array

Question

I have the following table:

CREATE TABLE fun (
    id uuid not null,
    tag varchar[] NOT NULL,
    CONSTRAINT fun_pkey PRIMARY KEY(id, tag)
);

CREATE UNIQUE INDEX idx_fun_id ON fun USING btree (id);

Then I inserted a data into the table

insert into fun (id, tag)
values('d7f17de9-c1e9-47ba-9e3d-cd1021c644d2', array['123','234'])

So currently, the value of my tag is ["123", "234"]

How can I add the value of the array, and ignore any of the existing varchar, only adding the non-existing one?

currently, this is how I approach it

update fun
set tag = tag || array['234','345']
where id = 'd7f17de9-c1e9-47ba-9e3d-cd1021c644d2'

but my tag will become ["123", "234", "234", "345"]. The value of 234 becomes a duplicated one. What I need to achieve is the value of the tag becomes ["123", "234", "345"]

user330315 · Accepted Answer · 2022-12-17 11:05:25Z

1

There is no built-in function to only append unique elements, but it's easy to write one:

create function append_unique(p_one text[], p_two text[])
  returns text[]
as
$$
  select array(select *
               from unnest(p_one)
               union
               select *
               from unnest(p_two));
$$
language sql
immutable;

Then you can use it like this:

update fun
  set tag = append_unique(tag,array['234','345'])
where id = 'd7f17de9-c1e9-47ba-9e3d-cd1021c644d2'

Note that this does not preserve the order of the items.

A function that preserves the order of the elements of the existing array and appends the elements of the second one in the order provided would be:

create function append_unique(p_one text[], p_two text[])
  returns text[]
as
$$
  select p_one||array(select x.item
                      from unnest(p_two) with ordinality as x(item,idx)
                      where x.item <> all (p_one)
                      order by x.idx);
$$
language sql
immutable;

answered Dec 17, 2022 at 11:05

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

Mark Over a year ago

the order is not important in my case. I have tried the answer and this is working well. thank you

Collectives™ on Stack Overflow

Postgres adding unexisting varchar array

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related