golang/pq : "pq: function uniq(integer[]) does not exist"-CodePudding

Here's my code (works fine with gin)

query :=`
UPDATE posts SET liked_by = uniq(array_append(liked_by, $1))
WHERE id = $2
RETURNING liked_by`

return p.DB.Exec(query, userID, post.ID)

The error I'm getting

pq: function uniq(integer[]) does not exist

My code works fine without uniq(). But the array is taking duplicate values. I wanna make sure my array only contains unique values.

CodePudding user response：

The issue is with your SQL (not Go or lib/pq); you can replicate the problem by running the following SQL:

select uniq('{1,2,3,4}'::integer[])

See this this question for a discussion of potential solutions, one of the simplest is probably:

select array_agg(distinct x) from unnest(array_append('{1,2,3,4}'::integer[], 4)) t(x)

Integrating this into your code gives something like (untested!):

query :=`
UPDATE posts SET 
liked_by = (select array_agg(distinct x) from unnest(array_append(liked_by, $1)) t(x))
WHERE id = $2
RETURNING liked_by
`