How to eliminate extra "NOT EXISTS"-query here?

Поиск
Список
Период
Сортировка
От Andreas Joseph Krogh
Тема How to eliminate extra "NOT EXISTS"-query here?
Дата
Msg-id VisenaEmail.39.cd03fed9d5bd1deb.18c06a48a33@origo-test01.app.internal.visena.net
обсуждение исходный текст
Ответы Re: How to eliminate extra "NOT EXISTS"-query here?
Список pgsql-general

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
Вложения

В списке pgsql-general по дате отправления:

Предыдущее
От: Zahir Lalani
Дата:
Сообщение: RE: Odd Shortcut behaviour in PG14
Следующее
От: Ron Johnson
Дата:
Сообщение: Re: Can user specification of a column value be required when querying a view ?