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

 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?

CodePudding user response：

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.