Thread: exclusive OR possible within a where clause?

I'm guessing that this isn't possible, but you guys are pretty smart. :)
Short version, is there a way to implement an exclusive OR in a where clause?

table1
dt1(timestamp)
---------
3 mins
5 mins
7 mins

table2
dt2(timestamp),   timedifference(interval)
---------------  ----------------------
4 mins              1 min ( always positive )

I want to join these two tables, grabbing the row from table 1 that
has a time equal to table2's record + interval, but table 2 could
have values that fall smack dab in the middle of two table1 records,
and I don't want both.

select
   t1.dt1
from
   table1 t1,
   table2 t2
where
   t2.dt2 + t2.timedifference = t1.dt1
     XOR ;-)
   t2.dt2 - t2.timedifference = t1.dt1

It's not important which of the two closest times
I get, but would like to get just one without being
messy.

Thanks for any ideas.  Maybe there's a great function out there
that solves this?

Dave

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