Update unique values into Postgres Array

Question

I have a situation where I update a Postgres table column which is of type bigint[]. This array should have unique numbers inside it whenever an update query is fired.

The query is as given below

UPDATE book_shelf 
SET book_id = book_id || array[CAST(:bookID AS BIGINT)], updated_at = now() 
WHERE user_id = :userID AND shelf_name = :shelfName

When the above query is fired, it simply adds the number into the array which I don't want to happen. It should hold only unique values. Please help me.

404 · Accepted Answer · 2018-11-16 09:47:25Z

1

You can check if it exists in the array before adding it:

UPDATE book_shelf 
SET book_id = CASE WHEN CAST(:bookID AS BIGINT) = ANY(book_id) THEN book_id ELSE ARRAY_APPEND(book_id, CAST(:bookID AS BIGINT)) END, updated_at = now() 
WHERE user_id = :userID AND shelf_name = :shelfName

Of course if updated_at should only be set if book_id is actually updated, then put the check in the WHERE clause so it's not updated unnecessarily:

UPDATE book_shelf 
SET book_id = ARRAY_APPEND(book_id, CAST(:bookID AS BIGINT)), updated_at = now() 
WHERE user_id = :userID
AND shelf_name = :shelfName
AND NOT CAST(:bookID AS BIGINT) = ANY(book_id)

answered Nov 16, 2018 at 9:47

404

8,7522 gold badges33 silver badges53 bronze badges

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

1 Comment

the_tech_maddy Over a year ago

Thanks a lot. Works like a charm

Collectives™ on Stack Overflow

Update unique values into Postgres Array

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related