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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.