exclusive OR possible within a where clause?

David Salisbury <salisbury@globe.gov> writes:

Short version, is there a way to implement an exclusive OR in a where clause?

The boolean <> operator will do the trick.

(x = y) <> (a = b)

regards, tom lane

On Thu, Oct 13, 2011 at 07:49:59PM -0400, Tom Lane wrote:

David Salisbury <salisbury@globe.gov> writes:

Short version, is there a way to implement an exclusive OR in a where clause?

The boolean <> operator will do the trick.

(x = y) <> (a = b)

regards, tom lane

Factoring in NULLable columns, that's:

(x IS NOT DISTINCT FROM y) <> (a IS NOT DISTINCT FROM b)

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On 10/14/11 10:58 AM, David Fetter wrote:

On Thu, Oct 13, 2011 at 07:49:59PM -0400, Tom Lane wrote:

David Salisbury<salisbury@globe.gov> writes:

Short version, is there a way to implement an exclusive OR in a where clause?

The boolean<> operator will do the trick.

(x = y)<> (a = b)

regards, tom lane

Factoring in NULLable columns, that's:

(x IS NOT DISTINCT FROM y)<> (a IS NOT DISTINCT FROM b)

Cheers,
David.

Thanks for the replies!

I should note ( for the mail list archives I guess )
that the above suggestions don't work. Both rows are
returned whether I use OR or <>, though maybe I'm not
understanding something. I'm not sure why <> would work either,
as all I can find is <> is the same as !=, which is
different than the fabled XOR I was hoping for. In fact
they would never equal.

But in the end it looks like wrapper sql around my output using
"select distinct.." should do the trick.

-Dave

On 10/17/11 12:15 PM, David Salisbury wrote:

<> is the same as !=, which is
different than the fabled XOR I was hoping for. In fact
they would never equal.

F != F -> false
F != T -> true
T != F -> true
T != T -> false

how is that different than XOR, assuming the arguments are booleans ?

--
john r pierce N 37, W 122
santa cruz ca mid-left coast

If you don't care about which row you get, how about adding a "LIMIT 1" to
your query? Don't know if that counts as "messy" or not... :)

On Mon, Oct 17, 2011 at 12:15 PM, David Salisbury <salisbury@globe.gov>wrote:

On 10/17/11 1:19 PM, John R Pierce wrote:

On 10/17/11 12:15 PM, David Salisbury wrote:

<> is the same as !=, which is
different than the fabled XOR I was hoping for. In fact
they would never equal.

F != F -> false
F != T -> true
T != F -> true
T != T -> false

how is that different than XOR, assuming the arguments are booleans ?

Perhaps what I'm hoping to do got munged. In essence it's equivalent of..

create table test ( something numeric );
insert into test values ( 1 );
insert into test values ( 2 );

select * from test where ( something = 1.5 + .5 ) or ( something = 1.5 - .5 );
something
-----------
1
2
(2 rows)

select * from test where ( something = 1.5 + .5 ) <> ( something = 1.5 - .5 );
something
-----------
1
2
(2 rows)

( which is of course equivalent of where something = 1 or something = 2 )

In my fabled XOR, I'd get the first one it matched, say something = 1, and the
something = 2 would then be ignored/dropped.

Dave

On 10/17/11 12:40 PM, David Salisbury wrote:

something
-----------
1
2

select * from test where ( something = 1.5 + .5 ) .XOR. ( something =
1.5 - .5 );

well, something[1] = 1, so thats FALSE .XOR. TRUE, which is TRUE
and, something[2] = 2, so thats TRUE .XOR. FALSE, which is also TRUE

no?

--
john r pierce N 37, W 122
santa cruz ca mid-left coast

On 10/17/11 2:12 PM, John R Pierce wrote:

On 10/17/11 12:40 PM, David Salisbury wrote:

something
-----------
1
2

select * from test where ( something = 1.5 + .5 ) .XOR. ( something =
1.5 - .5 );

well, something[1] = 1, so thats FALSE .XOR. TRUE, which is TRUE
and, something[2] = 2, so thats TRUE .XOR. FALSE, which is also TRUE

no?

Ah yes. I'm seeing the folly of my ways now (Doh!).

Distinct it is.

Thanks!

-ds

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of David Salisbury
Sent: Monday, October 17, 2011 3:41 PM
To: John R Pierce
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] exclusive OR possible within a where clause?

Perhaps what I'm hoping to do got munged. In essence it's equivalent of..

create table test ( something numeric ); insert into test values ( 1 );
insert into test values ( 2 );

select * from test where ( something = 1.5 + .5 ) or ( something = 1.5 - .5
);
something
-----------
1
2
(2 rows)

select * from test where ( something = 1.5 + .5 ) <> ( something = 1.5 - .5
);
something
-----------
1
2
(2 rows)

( which is of course equivalent of where something = 1 or something = 2 )

In my fabled XOR, I'd get the first one it matched, say something = 1, and
the something = 2 would then be ignored/dropped.

----------------------------------------------------------------------------
-------------

1) There is no concept of "FIRST" since you failed to include an ORDER BY
clause

2) Given that "something" can only take on a single value comparing it
against two separate (and different) values renders the difference between
OR and XOR meaningless.

If you want good help you need to give good examples - preferably real ones.

What you are trying to do has NOTHING to do with XOR. Provide a more
informative description of WHAT you are trying to do and additional REAL
sample data. From what you've described here, though, you probably want to
use WINDOW functions (RANK/ROW_NUMBER) in a sub-query and then look for only
those rows with RANK/ROW_NUMBER equal to 1.

David J.

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