Hello I want to update my data and my data looks like this:

list = {sad,dfgdf,vxcvxcv}

Which I used data type text[] in postgres. I want to add for example poc to this array ("{sad,dfgdf,vxcvxcv}") if it doesn't contain poc and if poc is already inside that array, I don't want to update it. Is there any option to do this?

CodePudding user response：

Use @> operator to check whether the array contains 'poc' and array_append to add it if not. Here is an illustration.

with t(x, y, arr) as
(
 values
 ('testa', 'BH06', '{sad,dfgdf,vxcvxcv}'::text[]),
 ('testb', 'BH07', '{das,fdgfd,abcbabc,poc}'),
 ('testc', 'BH08', '{}')
)
select x, y, 
    case when not arr @> '{poc}' then array_append(arr, 'poc') else arr end arr
from t;

Rsult:

Details on array operations here. Please note that double quotes are used for names and single quotes for string literals in Postgresql.

An update query, supposing that the target column in your table is called arr:

update the_table 
    set arr = array_append(arr, 'poc')
where not arr @> '{poc}';