Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dTCQ8-0004xI-Gy for pgsql-sql@arkaria.postgresql.org; Thu, 06 Jul 2017 19:26:36 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dTCQ7-0004OU-M4 for pgsql-sql@arkaria.postgresql.org; Thu, 06 Jul 2017 19:26:35 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1dTCP5-0002cs-Vg for pgsql-sql@postgresql.org; Thu, 06 Jul 2017 19:25:32 +0000 Received: from lb3-smtp-cloud3.xs4all.net ([194.109.24.30]) by magus.postgresql.org with esmtps (TLS1.0:DHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dTCOy-0005X9-Ry for pgsql-sql@postgresql.org; Thu, 06 Jul 2017 19:25:31 +0000 Received: from [10.0.0.130] ([92.108.133.107]) by smtp-cloud3.xs4all.net with ESMTP id hXRL1v00F2KBR2L01XRN2b; Thu, 06 Jul 2017 21:25:23 +0200 Subject: Re: Finding the negative To: Ed Rouse , "pgsql-sql@postgresql.org" References: From: Vincent Elschot Message-ID: Date: Thu, 6 Jul 2017 21:25:20 +0200 User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:52.0) Gecko/20100101 Thunderbird/52.2.1 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------DC5346A448E95F924D34ED91" Content-Language: nl List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org This is a multi-part message in MIME format. --------------DC5346A448E95F924D34ED91 Content-Type: text/plain; charset=windows-1252; format=flowed Content-Transfer-Encoding: 8bit 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 ) --------------DC5346A448E95F924D34ED91 Content-Type: text/html; charset=windows-1252 Content-Transfer-Encoding: 8bit



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