agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFinding the negative
3+ messages / 3 participants
[nested] [flat]
* Finding the negative
@ 2017-07-06 19:13 Ed Rouse <erouse@milner.com>
2017-07-06 19:25 ` Re: Finding the negative Vincent Elschot <vinny@xs4all.nl>
2017-07-06 19:43 ` Re: Finding the negative Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 3+ messages in thread
From: Ed Rouse @ 2017-07-06 19:13 UTC (permalink / raw)
To: pgsql-sql
Version 9.1.
I have the following query that brings back the correct number of lobs still asscociated with data in other tables:
select count(distinct(loid)) from pg_largeobject where (loid in (select content from attachments) or loid in (select content from idw_form_workflow_attachments));
in that the loid's in either table are found. What I need is to find the ones NOT in either table. I have tried using not in both with or/and and various parenthesis. I tried:
select count(distinct(loid)) from pg_largeobject where not ((loid in (select content from attachments) or loid in (select content from idw_form_workflow_attachments)));
This should work since, if the loid is not in the content of either table, it should be F or F = F, negated to T;
which I would think would then count, but I get back 0 instead of the rather large number I should get. This is all a prelude to removing lobs that are no longer referenced.
Any ideas on how to get the inverse of the working query? Thanks
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Finding the negative
2017-07-06 19:13 Finding the negative Ed Rouse <erouse@milner.com>
@ 2017-07-06 19:25 ` Vincent Elschot <vinny@xs4all.nl>
1 sibling, 0 replies; 3+ messages in thread
From: Vincent Elschot @ 2017-07-06 19:25 UTC (permalink / raw)
To: Ed Rouse <erouse@milner.com>; pgsql-sql
Op 06/07/2017 om 21:13 schreef Ed Rouse:
>
> Version 9.1.
>
> I have the following query that brings back the correct number of lobs
> still asscociated with data in other tables:
>
> select count(distinct(loid)) from pg_largeobject where (loid in
> (select content from attachments) or loid in (select content from
> idw_form_workflow_attachments));
>
> in that the loid’s in either table are found. What I need is to find
> the ones NOT in either table. I have tried using not in both with
> or/and and various parenthesis. I tried:
>
> select count(distinct(loid)) from pg_largeobject where not ((loid in
> (select content from attachments) or loid in (select content from
> idw_form_workflow_attachments)));
>
> This should work since, if the loid is not in the content of either
> table, it should be F or F = F, negated to T;
>
> which I would think would then count, but I get back 0 instead of the
> rather large number I should get. This is all a prelude to removing
> lobs that are no longer referenced.
>
> Any ideas on how to get the inverse of the working query? Thanks
>
Isn't this a case for EXCEPT, the reverse of UNION? Like in:
SELECT COUNT(*) FROM
(
SELECT foo FROM bar
EXCEPT
SELECT foo FROM cafe
)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Finding the negative
2017-07-06 19:13 Finding the negative Ed Rouse <erouse@milner.com>
@ 2017-07-06 19:43 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2017-07-06 19:43 UTC (permalink / raw)
To: Ed Rouse <erouse@milner.com>; +Cc: pgsql-sql
Ed Rouse <erouse@milner.com> writes:
> ... What I need is to find the ones NOT in either table. I have tried using not in both with or/and and various parenthesis. I tried:
> select count(distinct(loid)) from pg_largeobject where not ((loid in (select content from attachments) or loid in (select content from idw_form_workflow_attachments)));
> This should work since, if the loid is not in the content of either table, it should be F or F = F, negated to T;
> which I would think would then count, but I get back 0 instead of the rather large number I should get.
I'm suspicious that this means there's at least one NULL in those content
columns. That will cause the IN check to return either TRUE or NULL, not
FALSE. You could recast to use EXISTS, or explicitly exclude nulls while
selecting from the attachments tables.
regards, tom lane
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2017-07-06 19:43 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-07-06 19:13 Finding the negative Ed Rouse <erouse@milner.com>
2017-07-06 19:25 ` Vincent Elschot <vinny@xs4all.nl>
2017-07-06 19:43 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox