Postgresql case and testing boolean fields

Question

First: I'm running postgresql 8.2 and testing my queries on pgAdmin.

I have a table with some fields, say:

mytable(
  id integer,
  mycheck boolean,
  someText varchar(200));

Now, I want a query similary to this:

 select id,
  case when mycheck then (select name from tableA)
       else (select name from tableB) end as mySpecialName,
  someText;

I tried to run and get this:

ERROR: CASE types character varying and boolean cannot be matched
SQL state: 42804

And even trying to fool postgresql with

case (mycheck::integer) when 0 then

didn't work.

So, my question is: since sql doesn't have if, only case, how I'm suppose to do an if with a boolean field?

Answer 1

Your problem is a mismatch in your values (expressions after then and else), not your predicate (expression after when). Make sure that select name from tableA and select name from tableB return the same result type. mycheck is supposed to be a boolean.

I ran this query on PostgreSQL 9.0beta2, and (except for having to add from mytable to the SELECT statement as well as creating tables tableA and tableB), and it didn't yield any type errors. However, I get an error message much like the one you described when I run the following:

select case when true
           then 1
           else 'hello'::text 
       end;

The above yields:

ERROR:  CASE types text and integer cannot be matched