updating postgres table with unique Integer array

Question

I am not so good in queries. I have a table with column users which is of type 'users integer ARRAY'. For e.g. it looks like users[1,2]

Now I want to update this array with new value, but it has to be distinct. For e.g. if I want to add 3 to it then the output should be users[1,2,3] and if I add 1 to it then it should not make any update.

My current query to update this column is:

UPDATE userTable SET users = array_append(users, 3) WHERE user_id=1

I am not getting how and where to define so it only takes the distinct users.

Thanks in advance.

Gordon Linoff · Accepted Answer · 2021-02-22 12:59:24Z

4

Just don't do the update if the value is present:

UPDATE userTable
    SET users = array_append(users, 3)
    WHERE user_id = 1 AND NOT users @> array[3];

answered Feb 22, 2021 at 12:59

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

6 Comments

Ragu Over a year ago

What if initial 'users' array is empty? Because I tried this query with initially empty array, it did not update the table.

Gordon Linoff Over a year ago

Hmmm . . . I imagine the problem is that there is nothing to append if the array is empty. So, users doesn't change.

Ragu Over a year ago

So what shall I do if I want to update the 'users' array for the first time?

Gordon Linoff Over a year ago

@Ragu . . . It works: dbfiddle.uk/….

Ragu Over a year ago

this is how I am creating and inserting in a table. Can you please let me know where I am going wrong? : dbfiddle.uk/…

|

Collectives™ on Stack Overflow

updating postgres table with unique Integer array

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related