From: Vincent Elschot <vinny@xs4all.nl>
To: Ed Rouse <erouse@milner.com>
To: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: Finding the negative
Date: Thu, 6 Jul 2017 21:25:20 +0200
Message-ID: <e3c865da-5b87-6d64-4551-9f70659722ff@xs4all.nl> (raw)
In-Reply-To: <DE8D456CF535514BB21272D05C4A1C391E66AB2E@mbx029-e1-va-10.exch029.domain.local>
References: <DE8D456CF535514BB21272D05C4A1C391E66AB2E@mbx029-e1-va-10.exch029.domain.local>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: vinny@xs4all.nl, erouse@milner.com
Subject: Re: Finding the negative
In-Reply-To: <e3c865da-5b87-6d64-4551-9f70659722ff@xs4all.nl>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox