sql - POSTGRESQL 数组不包含值

Question

我是 sql 新手，我正在尝试将字符串附加到文本数组列，前提是该数组尚未包含该字符串:

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))

这是我的 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[]
);

即使我的用户范围 = 2、id = 3、company = 2 且数组为空，此查询也不起作用。

它不起作用是因为数组未定义还是我遗漏了什么？

PS:如果我删除 AND NOT ('/test2/test3/aaaa' = ANY (sharedfolders)) 它显然有效。

Answer 1

sharedfolders不能为 null 才能正常工作。使用空数组作为默认值

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 '{}'
);

和<> all更干净:

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)

如果需要将 null 作为默认值，则在比较之前合并:

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, '{}'))