Skip site navigation (1) Skip section navigation (2)

Re: Using CASE with a boolean value

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: tansley(at)law(dot)du(dot)edu
Cc: Postgresql Novice List <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Using CASE with a boolean value
Date: 2002-05-24 18:24:17
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
Tom Ansley <tansley(at)law(dot)du(dot)edu> writes:
> CASE booking.quiz 
> 	WHEN booking.quiz=false THEN 'No' 
> 	WHEN booking.quiz=true THEN 'Yes' 
> 	ELSE 'No' 

You seem to be confused about the two forms of CASE.  You can either
write boolean WHEN conditions or provide a value to be compared against
a series of alternative match values.  What you have here is an unholy
mixture of both, which would never have been accepted at all if
booking.quiz had not chanced to be a boolean value.  The system will
take it as (booking.quiz = (booking.quiz=false)), etc.  I'm far too lazy
to work out the exact implications of that, but it's probably not what
you want.

I'd write a CASE on a boolean value like this:

CASE WHEN booking.quiz THEN 'Yes' ELSE 'No' END

or if I wanted to distinguish UNKNOWN (NULL) as

CASE booking.quiz
  WHEN true THEN 'Yes'
  WHEN false THEN 'No'
  ELSE 'Unknown'

Or you could write it as

  WHEN booking.quiz=true THEN 'Yes'
  WHEN booking.quiz=false THEN 'No'
  ELSE 'Unknown'

which is actually what the system will expand the previous example into.
But writing it out seems un-idiomatic to me.  (I always look at 'boolean
= TRUE' kinds of tests as the mark of a beginner programmer who hasn't
quite absorbed the notion of a boolean value...)

			regards, tom lane

In response to


pgsql-novice by date

Next:From: Tom AnsleyDate: 2002-05-24 18:54:45
Subject: Re: Using CASE with a boolean value
Previous:From: Manfred KoizarDate: 2002-05-24 17:22:29
Subject: Re: query problem - get count in related table

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group