sqlarrayspostgresqlsql-update

Updating values in PostgreSQL array


I have some columns in PostgreSQL database that are array. I want to add a new value (in UPDATE) in it if the value don't exists, otherwise, don't add anything. I don't want to overwrite the current value of the array, only add the element to it.

Is it possible do this in plain SQL or do I need a function? I'm using PostgreSQL.


Solution

  • This should be as simple as this example for an integer array (integer[]):

    UPDATE tbl SET col = col || 5
    WHERE  (5 = ANY(col)) IS NOT TRUE;
    

    A WHERE clause like:

    WHERE  5 <> ALL(col)
    

    would also catch the case of an empty array '{}'::int[], but fail if a NULL value appears as element of the array.

    If your arrays never contain NULL as element, consider actual array operators, possibly supported by a GIN index.

    UPDATE tbl SET col = col || 5
    WHERE  NOT col @> '{5}';
    

    See: