Thread: BOOLEAN question
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 BerkusAglio Database SolutionsSan Francisco
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
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 BerkusAglio Database SolutionsSan Francisco
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)
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.
PerhapsSELECT true = ANY (SELECT boolcol FROM ...);
orSELECT 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
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 informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
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
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.
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
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 ...); > > >