How to eliminate extra "NOT EXISTS"-query here? - Mailing list pgsql-general

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