1

I had tried to create unique index for charactery varying[] column. I create a function modified from this post which type was integer[]. When append a lists of string, the array results in duplicate.

Code

create function sort_array(character varying[]) returns character varying[] as $$
    select array_agg(n) from (select n from unnest($1) as t(n) order by n) as a;
$$ language sql immutable;

CREATE UNIQUE INDEX ip_uniq ON table.external (sort_array(ip));

When update

UPDATE table.external SET ip = array_cat(ip,'{1.1.1.1,2.2.2.2}') WHERE id = NO_ID

Result

ip = {1.1.1.1,2.2.2.2,1.1.1.1,2.2.2.2}

Expected

ip = {1.1.1.1,2.2.2.2}

1
  • The sorted array is only stored in the index, not the table because your UPDATE statement does use the sort_array function. Commented Mar 13, 2020 at 8:27

0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.