updating postgres table with unique Integer array

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.

Answer

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];

Advertisement

Answer