How to add non duplicate items inside an array in PostgreSQL?

Question

 question_id | person_id |      question_title      |     question_source      | question_likes | question_dislikes |           created_at            
-------------+-----------+--------------------------+--------------------------+----------------+-------------------+---------------------------------
           2 | 2         | This is question Title 1 | This is question Video 1 |                |                   | 2021-11-11 10:32:53.93505+05:30
           3 | 3         | This is question Title 1 | This is question Video 1 |                |                   | 2021-11-11 10:32:58.69947+05:30
           1 | 1         | This is question Title 1 | This is question Video 1 | {2}            | {1}               | 2021-11-11 10:32:45.81733+05:30

I'm trying to add values inside the question_likes and question_dislikes which are arrays. I'm not able to figure out the query to add only non duplicate items inside the array using sql. I want all items in the array to be unique.

Below is the query i used to create the questions table:

CREATE TABLE questions(
    question_id BIGSERIAL PRIMARY KEY,
    person_id VARCHAR(50) REFERENCES person(person_id) NOT NULL,
    question_title VARCHAR(100) NOT NULL,
    question_source VARCHAR(100),
    question_likes TEXT[] UNIQUE,
    question_dislikes TEXT[] UNIQUE,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

UPDATE questions 
  SET question_likes = array_append(question_likes,$1) 
WHERE question_id=$2 
RETURNING *

What changes can i make to the above query to make it work?

This would be easily done with a unique constraint in a properly normalized data model — user330315
– user330315, Commented Nov 11, 2021 at 6:56
@a_horse_with_no_name i have added the unique constraint when i created the table. I have updated the question with the query i used to create table — Joe
– Joe, Commented Nov 11, 2021 at 7:05
I was referring to a unique constraint in a properly normalized model using a one-to-many relationship. A unique constraint over an array (in a de-normalized model) doesn't really help you. It prevents inserting e.g. {1,2} twice in two different rows. But it doesn't even prevent inserting {1,2} and {2,1} in two rows and it certainly doesn't prevent {1,1} in a single column. — user330315
– user330315, Commented Nov 11, 2021 at 7:09

user330315 · Accepted Answer · 2021-11-11 07:13:06Z

5

This is a drawback of using a de-normalized model. Just because Postgres supports arrays, doesn't mean that they are a good choice to solve problems that are better solved using best practices of relational data modeling.

However, in your case you can simply prevent this by adding an additional condition to your WHERE clause:

UPDATE questions 
  SET question_likes = array_append(question_likes,$1) 
WHERE question_id=$2 
  AND $1 <> ALL(question_likes)
RETURNING *

This prevents the update completely if the value of $1 is found anywhere in the array.

answered Nov 11, 2021 at 7:13

user330315

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

Collectives™ on Stack Overflow

How to add non duplicate items inside an array in PostgreSQL?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related