pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedBOOLEAN question
10+ messages / 6 participants
[nested] [flat]
* BOOLEAN question
@ 2002-10-29 01:06 Josh Berkus <josh@agliodbs.com>
0 siblings, 2 replies; 10+ messages in thread
From: Josh Berkus @ 2002-10-29 01:06 UTC (permalink / raw)
To: pgsql-sql
Folks,
Odd question: I have a query that returns a set of none to several rows. One
column in these rows is BOOLEAN. I want the query to return:
1) TRUE if *any* of the BOOLEAN values is TRUE;
2) FALSE if *all* of the BOOLEAN values are FALSE;
3) FALSE or NULL if no rows are returned.
I thought that I could do this through a MAX(boolean), but as it turns out,
MAX(boolean) has not been defined in the standard 7.2.3. distro. I could
define MAX(boolean), but I'm wondering if I'm missing something obvious.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BOOLEAN question
@ 2002-10-29 01:26 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 1 reply; 10+ messages in thread
From: Stephan Szabo @ 2002-10-29 01:26 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql
On Mon, 28 Oct 2002, Josh Berkus wrote:
> Odd question: I have a query that returns a set of none to several rows. One
> column in these rows is BOOLEAN. I want the query to return:
> 1) TRUE if *any* of the BOOLEAN values is TRUE;
> 2) FALSE if *all* of the BOOLEAN values are FALSE;
> 3) FALSE or NULL if no rows are returned.
>
> I thought that I could do this through a MAX(boolean), but as it turns out,
> MAX(boolean) has not been defined in the standard 7.2.3. distro. I could
> define MAX(boolean), but I'm wondering if I'm missing something obvious.
Well, you might be better off making a max(bool), but a not
super-efficient version might be:
max(case when col then 1 else 0 end)=1
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BOOLEAN question
@ 2002-10-29 01:29 Josh Berkus <josh@agliodbs.com>
parent: Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 2 replies; 10+ messages in thread
From: Josh Berkus @ 2002-10-29 01:29 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: pgsql-sql
Stephan,
> Well, you might be better off making a max(bool), but a not
> super-efficient version might be:
> max(case when col then 1 else 0 end)=1
Good, I'm not just brain-dead. I did figure out another way to do it, but if
I wrote a MAX(bool), what are the chances it would get added to the core? I
don't see any good reason not to have one.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BOOLEAN question
@ 2002-10-29 03:53 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 10+ messages in thread
From: Stephan Szabo @ 2002-10-29 03:53 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql
On Mon, 28 Oct 2002, Josh Berkus wrote:
> Stephan,
>
> > Well, you might be better off making a max(bool), but a not
> > super-efficient version might be:
> > max(case when col then 1 else 0 end)=1
>
> Good, I'm not just brain-dead. I did figure out another way to do it, but if
> I wrote a MAX(bool), what are the chances it would get added to the core? I
> don't see any good reason not to have one.
Don't know really. I guess if we're saying that true>false it might
make sense, although the notion of max(bool) seems odd to me, especially
since it only easily handles an ANY case.
Speaking of which, a more expensive but possibly nicer looking way would
be something like
true=ANY(select col from <whatever>)
(which of course also generalizes into =ALL fairly easily)
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BOOLEAN question
@ 2002-10-29 18:27 Tom Lane <tgl@sss.pgh.pa.us>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 1 reply; 10+ messages in thread
From: Tom Lane @ 2002-10-29 18:27 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: pgsql-sql
Josh Berkus <josh@agliodbs.com> writes:
> Odd question: I have a query that returns a set of none to several rows. One
> column in these rows is BOOLEAN. I want the query to return:
> 1) TRUE if *any* of the BOOLEAN values is TRUE;
> 2) FALSE if *all* of the BOOLEAN values are FALSE;
> 3) FALSE or NULL if no rows are returned.
Perhaps
SELECT true = ANY (SELECT boolcol FROM ...);
or
SELECT true IN (SELECT boolcol FROM ...);
Which is not to say that MAX(bool) might not be a nicer solution;
but you can definitely do it with SQL-spec constructs.
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BOOLEAN question
@ 2002-10-29 20:32 Josh Berkus <josh@agliodbs.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: Josh Berkus @ 2002-10-29 20:32 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; josh@agliodbs.com; +Cc: pgsql-sql
Tom,
> Perhaps
> SELECT true = ANY (SELECT boolcol FROM ...);
> or
> SELECT true IN (SELECT boolcol FROM ...);
>
> Which is not to say that MAX(bool) might not be a nicer solution;
> but you can definitely do it with SQL-spec constructs.
Based on some rough testing,
SELECT true = ANY ( SELECT boolcol FROM complex query )
Is marginlly faster than
SELECT max(boolcol) FROM complex query
With a custom MAX(boolean) function.
So I'll stick to ANY().
-Josh
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BOOLEAN question
@ 2002-10-29 22:19 Peter Eisentraut <peter_e@gmx.net>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 1 reply; 10+ messages in thread
From: Peter Eisentraut @ 2002-10-29 22:19 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Stephan Szabo <sszabo@megazone23.bigpanda.com>; pgsql-sql
Josh Berkus writes:
> I wrote a MAX(bool), what are the chances it would get added to the core? I
> don't see any good reason not to have one.
One reason not to have one is that Boolean values are not naturally
ordered, so a maximum doesn't exist. (Yes, I know there are comparison
operators defined for boolean, but that doesn't make it right.)
What seems more reasonable is to define conjuntion and disjunction
aggregates, which would mostly do the same thing but their semantics
wouldn't be as controversial.
--
Peter Eisentraut peter_e@gmx.net
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BOOLEAN question
@ 2002-10-30 02:22 Bruno Wolff III <bruno@wolff.to>
parent: Peter Eisentraut <peter_e@gmx.net>
0 siblings, 0 replies; 10+ messages in thread
From: Bruno Wolff III @ 2002-10-30 02:22 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: Josh Berkus <josh@agliodbs.com>; Stephan Szabo <sszabo@megazone23.bigpanda.com>; pgsql-sql
On Tue, Oct 29, 2002 at 23:19:55 +0100,
Peter Eisentraut <peter_e@gmx.net> wrote:
> Josh Berkus writes:
>
> > I wrote a MAX(bool), what are the chances it would get added to the core? I
> > don't see any good reason not to have one.
>
> One reason not to have one is that Boolean values are not naturally
> ordered, so a maximum doesn't exist. (Yes, I know there are comparison
> operators defined for boolean, but that doesn't make it right.)
>
> What seems more reasonable is to define conjuntion and disjunction
> aggregates, which would mostly do the same thing but their semantics
> wouldn't be as controversial.
However they would be slow. If you use an aggregate you don't get to short
circuit the calculation. And for even distributed true and false values,
most of the time the aggregate result would be known after just a few rows
were checked.
When checking for such values in long lived tables where one of the values
occurs seldomly, using a partial index and a select could be a big win.
I don't think this will work in the case that started this thread since
it postulated checking the output of a complex query. However even in
this case it may be worthwhile to rewrite the query as an exists test
for boolcol to be the short circuit value so that the complex query
need not be fully calculated.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BOOLEAN question
@ 2002-10-30 16:26 Jean-Luc Lachance <jllachan@nsd.ca>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 10+ messages in thread
From: Jean-Luc Lachance @ 2002-10-30 16:26 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
Why not simply:
SELECT COALESCE( (SELECT true FROM ... WHERE boolcol LIMIT 1), FALSE);
JLL
Josh Berkus wrote:
>
> Tom,
>
> > Perhaps
> > SELECT true = ANY (SELECT boolcol FROM ...);
> > or
> > SELECT true IN (SELECT boolcol FROM ...);
> >
> > Which is not to say that MAX(bool) might not be a nicer solution;
> > but you can definitely do it with SQL-spec constructs.
>
> Based on some rough testing,
>
> SELECT true = ANY ( SELECT boolcol FROM complex query )
>
> Is marginlly faster than
>
> SELECT max(boolcol) FROM complex query
>
> With a custom MAX(boolean) function.
>
> So I'll stick to ANY().
>
> -Josh
>
> ______AGLIO DATABASE SOLUTIONS___________________________
> Josh Berkus
> Complete information technology josh@agliodbs.com
> and data management solutions (415) 565-7293
> for law firms, small businesses fax 621-2533
> and non-profit organizations. San Francisco
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BOOLEAN question
@ 2002-10-30 16:38 Jean-Luc Lachance <jllachan@nsd.ca>
parent: Jean-Luc Lachance <jllachan@nsd.ca>
0 siblings, 0 replies; 10+ messages in thread
From: Jean-Luc Lachance @ 2002-10-30 16:38 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
Of course, I meant
SELECT COALESCE( (SELECT true FROM ... WHERE ... AND boolcol LIMIT 1),
FALSE);
Jean-Luc Lachance wrote:
>
> Why not simply:
>
> SELECT COALESCE( (SELECT true FROM ... WHERE boolcol LIMIT 1), FALSE);
>
> JLL
>
> Josh Berkus wrote:
> >
> > Tom,
> >
> > > Perhaps
> > > SELECT true = ANY (SELECT boolcol FROM ...);
> > > or
> > > SELECT true IN (SELECT boolcol FROM ...);
> > >
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2002-10-30 16:38 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-10-29 01:06 BOOLEAN question Josh Berkus <josh@agliodbs.com>
2002-10-29 01:26 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-10-29 01:29 ` Josh Berkus <josh@agliodbs.com>
2002-10-29 03:53 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-10-29 22:19 ` Peter Eisentraut <peter_e@gmx.net>
2002-10-30 02:22 ` Bruno Wolff III <bruno@wolff.to>
2002-10-29 18:27 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-10-29 20:32 ` Josh Berkus <josh@agliodbs.com>
2002-10-30 16:26 ` Jean-Luc Lachance <jllachan@nsd.ca>
2002-10-30 16:38 ` Jean-Luc Lachance <jllachan@nsd.ca>
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