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}

Abel
  • 1,494
  • 3
  • 21
  • 32
  • The sorted array is only stored in the index, not the table because your UPDATE statement does use the `sort_array` function. –  Mar 13 '20 at 08:27

0 Answers0