Postgresql extension intarray uniq

Question

My sql structure has a field which is array.
The value is

{5010011,5010031,5010041,5010081}

I execute a query.

Update users 
  set faces=uniq(array_cat(faces,ARRAY[5010011,5010031,5010041,5010081]))

I get the result is

{5010011,5010031,5010041,5010081,5010011,5010031,5010041,5010081}

I use the uniq() function, but it does not work. I want to remove the duplicate value in array.

user330315 · Accepted Answer · 2018-01-20 10:42:01Z

1

Quote from the manual

uniq(int[]) int[] remove adjacent duplicates

^{(emphasis mine)}

So it only removes duplicates that are adjacent (a value immediately followed by the same value).

The manual also shows how to use the function: you need to sort the array first, so that the duplicate values wind up next to each other: uniq(sort('{1,2,3,2,1}'::int[]))

So in your case:

Update users 
  set  faces = uniq(
                   sort(array_cat(faces, ARRAY[5010011,5010031,5010041,5010081]))
                   )

answered Jan 20, 2018 at 10:42

user330315

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

2 Comments

Wong Manlok Over a year ago

Thanks for quick reply. I don't sort the array. So the uniq function is not work.

user330315 Over a year ago

@WongManlok: well, you have to sort the array before applying the uniq() function as I have shown in my answer. The better solution however would be to normalize your data model and then create a proper unique index

Collectives™ on Stack Overflow

Postgresql extension intarray uniq

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related