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.
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:
UPDATE token SET grammar = grammar || 'JUNCTION' WHERE ('JUNCTION' = ANY(grammar)) IS NOT TRUE;. This is the SQL modified for my case, but if fails in the grammar || 'JUNCTION'. The type of array is VARCHAR....grammar = grammar || 'JUNCTION'::VARCHAR.