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 stuff s 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 stuff s 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
