POSTGRESQL array does not contain value

Question

I'm new to sql and I'm trying to append a string to a text array column only if the array does not already contain the string :

UPDATE users 
SET sharedfolders = array_append(sharedfolders, '/test2/test3/aaaa')
WHERE companyid = 2
AND scope = 2
AND id = 3
AND NOT ('/test2/test3/aaaa' = ANY (sharedfolders))

Here is my table :

CREATE TABLE users (
   id            SERIAL PRIMARY KEY,
   companyid     INT REFERENCES companies (id) ON UPDATE CASCADE ON DELETE CASCADE,
   email         VARCHAR UNIQUE,
   lastname      VARCHAR(50),
   firstname     VARCHAR(50),
   password      VARCHAR,
   scope         SMALLINT,
   sharedfolders TEXT[]
);

This query does not work even if I have an user with scope = 2, id = 3, company = 2 and an empty array.

Is it not working because the array is not defined or am I missing something ?

PS: if I remove the AND NOT ('/test2/test3/aaaa' = ANY (sharedfolders)) it's obviously working.

Clodoaldo Neto · Accepted Answer · 2017-02-16 15:50:40Z

2

sharedfolders can not be null for it to work. Use an empty array as default value

create table users (
   id            int primary key,
   companyid     int,
   email         varchar unique,
   lastname      varchar(50),
   firstname     varchar(50),
   password      varchar,
   scope         smallint,
   sharedfolders text[] default '{}'
);

And <> all is cleaner:

update users 
set sharedfolders = array_append(sharedfolders, '/test2/test3/aaaa')
where companyid = 2
and scope = 2
and id = 3
and '/test2/test3/aaaa' <> all (sharedfolders)

If it is necessary to have null as default then coalesce before comparing:

update users 
set sharedfolders = array_append(sharedfolders, '/test2/test3/aaaa')
where companyid = 2
and scope = 2
and id = 3
and '/test2/test3/aaaa' <> all (coalesce(sharedfolders, '{}'))

edited Feb 16, 2017 at 15:50

answered Feb 16, 2017 at 15:46

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

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

4 Comments

Gatsbill Over a year ago

you beat me by 1 sec, just did it and it works, thanks anyway. Will go with <> too. Do you know if it performs well with multiple rows?

Vao Tsun Over a year ago

select array_append(null::text[], '/test2/test3/aaaa') gives an array?..

Vao Tsun Over a year ago

ah - you meant it cant be null for where clause to work

jaibhavaya Over a year ago

this also will not use array indexes.

Collectives™ on Stack Overflow

POSTGRESQL array does not contain value

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related