From erouse@milner.com Thu Jul  6 19:13:09 2017
Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtp (Exim 4.84_2)
	(envelope-from
 <pgsql-sql-owner+M39208=pgsql+2Dsql=arkaria.postgresql.org@postgresql.org>)
	id 1dTCFH-0004Ol-5M
	for pgsql-sql@arkaria.postgresql.org; Thu, 06 Jul 2017 19:15:23 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
	by malur.postgresql.org with smtp (Exim 4.84_2)
	(envelope-from
 <pgsql-sql-owner+M39208=pgsql+2Dsql=arkaria.postgresql.org@postgresql.org>)
	id 1dTCFG-0002Cy-1u
	for pgsql-sql@arkaria.postgresql.org; Thu, 06 Jul 2017 19:15:22 +0000
Received: from makus.postgresql.org ([2001:4800:1501:1::229])
	by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256)
	(Exim 4.84_2)
	(envelope-from <erouse@milner.com>)
	id 1dTCDD-0000C5-Ky
	for pgsql-sql@postgresql.org; Thu, 06 Jul 2017 19:13:15 +0000
Received: from hub029-va-8.exch029.serverdata.net ([199.193.200.199])
	by makus.postgresql.org with esmtps (TLS1.0:ECDHE_RSA_AES_256_CBC_SHA1:256)
	(Exim 4.84_2)
	(envelope-from <erouse@milner.com>)
	id 1dTCD9-0002nJ-P6
	for pgsql-sql@postgresql.org; Thu, 06 Jul 2017 19:13:14 +0000
Received: from MBX029-E1-VA-10.exch029.domain.local ([10.216.105.60]) by
 HUB029-VA-8.exch029.domain.local ([10.216.105.236]) with mapi id
 14.03.0319.002; Thu, 6 Jul 2017 12:13:09 -0700
From: Ed Rouse <erouse@milner.com>
To: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Subject: Finding the negative
Thread-Topic: Finding the negative
Thread-Index: AdL2i+erhr9cSSp4Q02wX4frrlE4rQ==
Date: Thu, 6 Jul 2017 19:13:09 +0000
Message-ID: 
 <DE8D456CF535514BB21272D05C4A1C391E66AB2E@mbx029-e1-va-10.exch029.domain.local>
Accept-Language: en-US
Content-Language: en-US
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
x-originating-ip: [209.155.237.189]
Content-Type: multipart/alternative;
	boundary="_000_DE8D456CF535514BB21272D05C4A1C391E66AB2Embx029e1va10exc_"
MIME-Version: 1.0
List-Archive: <http://archives.postgresql.org/pgsql-sql>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-sql.postgresql.org>
List-Owner: <mailto:pgsql-sql-owner@postgresql.org>
List-Post: <mailto:pgsql-sql@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-sql>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
X-Mailing-List: pgsql-sql
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

--_000_DE8D456CF535514BB21272D05C4A1C391E66AB2Embx029e1va10exc_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Version 9.1.
I have the following query that brings back the correct number of lobs stil=
l asscociated with data in other tables:

select count(distinct(loid)) from pg_largeobject where (loid in (select con=
tent from attachments) or loid in (select content from idw_form_workflow_at=
tachments));

in that the loid's in either table are found. What I need is to find the on=
es NOT in either table. I have tried using not in both with or/and  and var=
ious parenthesis. I tried:

select count(distinct(loid)) from pg_largeobject where not ((loid in (selec=
t content from attachments) or loid in (select content from idw_form_workfl=
ow_attachments)));

This should work since, if the loid is not in the content of either table, =
it should be F or F =3D F, negated to T;
which I would think would then count, but I get back 0 instead of the rathe=
r large number I should get. This is all a prelude to removing lobs that ar=
e no longer referenced.

Any ideas on how to get the inverse of the working query? Thanks

--_000_DE8D456CF535514BB21272D05C4A1C391E66AB2Embx029e1va10exc_
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" xmlns:o=3D"urn:schemas-micr=
osoft-com:office:office" xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" xmlns=3D"http:=
//www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Dus-ascii"=
>
<meta name=3D"Generator" content=3D"Microsoft Word 12 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
	{font-family:"Cambria Math";
	panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
	{font-family:Calibri;
	panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0in;
	margin-bottom:.0001pt;
	font-size:11.0pt;
	font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
	{mso-style-priority:99;
	color:blue;
	text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{mso-style-priority:99;
	color:purple;
	text-decoration:underline;}
span.EmailStyle17
	{mso-style-type:personal-compose;
	font-family:"Calibri","sans-serif";
	color:windowtext;}
.MsoChpDefault
	{mso-style-type:export-only;}
@page WordSection1
	{size:8.5in 11.0in;
	margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
	{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext=3D"edit">
<o:idmap v:ext=3D"edit" data=3D"1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang=3D"EN-US" link=3D"blue" vlink=3D"purple">
<div class=3D"WordSection1">
<p class=3D"MsoNormal">Version 9.1.<o:p></o:p></p>
<p class=3D"MsoNormal">I have the following query that brings back the corr=
ect number of lobs still asscociated with data in other tables:<o:p></o:p><=
/p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">select count(distinct(loid)) from pg_largeobject whe=
re (loid in (select content from attachments) or loid in (select content fr=
om idw_form_workflow_attachments));<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">in that the loid&#8217;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 &nbsp;and various parenthesis. I tried:<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">select count(distinct(loid)) from pg_largeobject whe=
re not ((loid in (select content from attachments) or loid in (select conte=
nt from idw_form_workflow_attachments)));<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">This should work since, if the loid is not in the co=
ntent of either table, it should be F or F =3D F, negated to T;<o:p></o:p><=
/p>
<p class=3D"MsoNormal">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 t=
o removing lobs that are no longer referenced.
<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">Any ideas on how to get the inverse of the working q=
uery? Thanks<o:p></o:p></p>
</div>
</body>
</html>

--_000_DE8D456CF535514BB21272D05C4A1C391E66AB2Embx029e1va10exc_--




From vinny@xs4all.nl Thu Jul  6 19:25:20 2017
Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtp (Exim 4.84_2)
	(envelope-from
 <pgsql-sql-owner+M39209=pgsql+2Dsql=arkaria.postgresql.org@postgresql.org>)
	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
 <pgsql-sql-owner+M39209=pgsql+2Dsql=arkaria.postgresql.org@postgresql.org>)
	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 <vinny@xs4all.nl>)
	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 <vinny@xs4all.nl>)
	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 <erouse@milner.com>,
 "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
References: 
 <DE8D456CF535514BB21272D05C4A1C391E66AB2E@mbx029-e1-va-10.exch029.domain.local>
From: Vincent Elschot <vinny@xs4all.nl>
Message-ID: <e3c865da-5b87-6d64-4551-9f70659722ff@xs4all.nl>
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: 
 <DE8D456CF535514BB21272D05C4A1C391E66AB2E@mbx029-e1-va-10.exch029.domain.local>
Content-Type: multipart/alternative;
 boundary="------------DC5346A448E95F924D34ED91"
Content-Language: nl
List-Archive: <http://archives.postgresql.org/pgsql-sql>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-sql.postgresql.org>
List-Owner: <mailto:pgsql-sql-owner@postgresql.org>
List-Post: <mailto:pgsql-sql@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-sql>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
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

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html;
      charset=windows-1252">
  </head>
  <body text="#000000" bgcolor="#FFFFFF">
    <p><br>
    </p>
    <br>
    <div class="moz-cite-prefix">Op 06/07/2017 om 21:13 schreef Ed
      Rouse:<br>
    </div>
    <blockquote type="cite"
cite="mid:DE8D456CF535514BB21272D05C4A1C391E66AB2E@mbx029-e1-va-10.exch029.domain.local">
      <meta http-equiv="Content-Type" content="text/html;
        charset=windows-1252">
      <meta name="Generator" content="Microsoft Word 12 (filtered
        medium)">
      <style><!--
/* Font Definitions */
@font-face
	{font-family:"Cambria Math";
	panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
	{font-family:Calibri;
	panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0in;
	margin-bottom:.0001pt;
	font-size:11.0pt;
	font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
	{mso-style-priority:99;
	color:blue;
	text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{mso-style-priority:99;
	color:purple;
	text-decoration:underline;}
span.EmailStyle17
	{mso-style-type:personal-compose;
	font-family:"Calibri","sans-serif";
	color:windowtext;}
.MsoChpDefault
	{mso-style-type:export-only;}
@page WordSection1
	{size:8.5in 11.0in;
	margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
	{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
      <div class="WordSection1">
        <p class="MsoNormal">Version 9.1.<o:p></o:p></p>
        <p class="MsoNormal">I have the following query that brings back
          the correct number of lobs still asscociated with data in
          other tables:<o:p></o:p></p>
        <p class="MsoNormal"><o:p> </o:p></p>
        <p class="MsoNormal">select count(distinct(loid)) from
          pg_largeobject where (loid in (select content from
          attachments) or loid in (select content from
          idw_form_workflow_attachments));<o:p></o:p></p>
        <p class="MsoNormal"><o:p> </o:p></p>
        <p class="MsoNormal">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:<o:p></o:p></p>
        <p class="MsoNormal"><o:p> </o:p></p>
        <p class="MsoNormal">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)));<o:p></o:p></p>
        <p class="MsoNormal"><o:p> </o:p></p>
        <p class="MsoNormal">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;<o:p></o:p></p>
        <p class="MsoNormal">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.
          <o:p></o:p></p>
        <p class="MsoNormal"><o:p> </o:p></p>
        <p class="MsoNormal">Any ideas on how to get the inverse of the
          working query? Thanks<o:p></o:p></p>
      </div>
    </blockquote>
    <br>
    Isn't this a case for EXCEPT, the reverse of UNION? Like in:<br>
    SELECT COUNT(*) FROM<br>
    (<br>
    SELECT foo  FROM bar<br>
    EXCEPT<br>
    SELECT foo FROM cafe<br>
    )<br>
  </body>
</html>

--------------DC5346A448E95F924D34ED91--




From tgl@sss.pgh.pa.us Thu Jul  6 19:43:23 2017
Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtp (Exim 4.84_2)
	(envelope-from
 <pgsql-sql-owner+M39210=pgsql+2Dsql=arkaria.postgresql.org@postgresql.org>)
	id 1dTChY-0005qE-Jh
	for pgsql-sql@arkaria.postgresql.org; Thu, 06 Jul 2017 19:44:36 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
	by malur.postgresql.org with smtp (Exim 4.84_2)
	(envelope-from
 <pgsql-sql-owner+M39210=pgsql+2Dsql=arkaria.postgresql.org@postgresql.org>)
	id 1dTChX-0006uL-H2
	for pgsql-sql@arkaria.postgresql.org; Thu, 06 Jul 2017 19:44: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 <tgl@sss.pgh.pa.us>)
	id 1dTCgY-00057c-BG
	for pgsql-sql@postgresql.org; Thu, 06 Jul 2017 19:43:34 +0000
Received: from sss.pgh.pa.us ([66.207.139.130])
	by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256)
	(Exim 4.84_2)
	(envelope-from <tgl@sss.pgh.pa.us>)
	id 1dTCgQ-0005sa-Ru
	for pgsql-sql@postgresql.org; Thu, 06 Jul 2017 19:43:33 +0000
Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1])
	by sss.pgh.pa.us (8.14.4/8.14.4) with ESMTP id v66JhNEi026803;
	Thu, 6 Jul 2017 15:43:23 -0400
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Ed Rouse <erouse@milner.com>
cc: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Subject: Re: Finding the negative
In-reply-to: 
 <DE8D456CF535514BB21272D05C4A1C391E66AB2E@mbx029-e1-va-10.exch029.domain.local>
References: 
 <DE8D456CF535514BB21272D05C4A1C391E66AB2E@mbx029-e1-va-10.exch029.domain.local>
Comments: In-reply-to Ed Rouse <erouse@milner.com>
	message dated "Thu, 06 Jul 2017 19:13:09 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <26801.1499370203.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Thu, 06 Jul 2017 15:43:23 -0400
Message-ID: <26802.1499370203@sss.pgh.pa.us>
List-Archive: <http://archives.postgresql.org/pgsql-sql>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-sql.postgresql.org>
List-Owner: <mailto:pgsql-sql-owner@postgresql.org>
List-Post: <mailto:pgsql-sql@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-sql>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
X-Mailing-List: pgsql-sql
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

Ed Rouse <erouse@milner.com> writes:
> ... What I need is to find the ones NOT in either table. I have tried usi=
ng not in both with or/and  and various parenthesis. I tried:

> select count(distinct(loid)) from pg_largeobject where not ((loid in (sel=
ect content from attachments) or loid in (select content from idw_form_work=
flow_attachments)));

> This should work since, if the loid is not in the content of either table=
, it should be F or F =3D F, negated to T;
> which I would think would then count, but I get back 0 instead of the rat=
her 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


--=20
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql