Hi, I'm testing if some dataset contains an array of elements and want to return all “not containing the specified array”, including entries in master table not being referenced.
I have the following schema: drop table if exists stuff; drop table if exists test; CREATE TABLE test( id varchar primary key ); create table stuff( id serial primary key, test_id varchar NOT NULL REFERENCES test(id), v varchar not null, unique (test_id, v) ); INSERT INTO test(id) values ('a'); INSERT INTO test(id) values ('b'); INSERT INTO test(id) values ('c'); INSERT INTO test(id) values ('d'); INSERT INTO stuff(test_id, v) values ('a', 'x') ; INSERT INTO stuff(test_id, v) values ('b', 'x') , ('b', 'y') ; INSERT INTO stuff(test_id, v) values ('c', 'x') , ('c', 'y') , ('c', 'z') ; select * from test t WHERE NOT ARRAY['x']::varchar[] <@ (select array_agg(s.v) from stuff s WHERE s. test_id= t.id) ; select * from test t WHERE NOT ARRAY['x', 'y']::varchar[] <@ (select array_agg(s.v) from stuff s WHERE s.test_id = t.id) ; select * from test t WHERE NOT ARRAY['x', 'y', 'z']::varchar[] <@ (select array_agg(s.v) from stuff s WHERE s.test_id = t.id) ; select * from test t WHERE NOT ARRAY ['x', 'y', 'z', 't']::varchar[] <@ (select array_agg(s.v) from stuffs WHERE s.test_id = t.id) ; -- This works, but I'd rather not do the extra EXISTS select * from test t WHERE (NOT ARRAY ['x', 'y', 'z', 't']::varchar[] <@ (select array_agg(s.v) from stuffs WHERE s.test_id = t.id) OR NOT EXISTS ( select * from stuff s where s.test_id = t.id ) ) ; So, I want to return all entries in test not having any of ARRAY ['x', 'y', 'z', 't'] referenced in the table stuff, and I'd like to have test.id="d" returned as well, but in order to do that I need to execute the “or not exists”-query. Is it possible to avoid that? -- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andr...@visena.com <mailto:andr...@visena.com> www.visena.com <https://www.visena.com> <https://www.visena.com>