Thread: Confused about CASE

Hello,

I was writing a statement retrieve dependency information out of the system
catalog, when I noticed something that I didn't expect.

I wanted to use the following statement to "translate" the relkind column to a
more descriptive value:

select c.relname
        case
          when c.relkind in ('t','r') then 'table'
          when c.relkind = 'i' then 'index'
          when c.relkind = 'S' then 'sequence'
          when c.relkind = 'v' then 'view'
          else c.relkind
        end as mykind
from pg_class c
;

The idea is that for anything else than 't', 'r', 'i', 'S' or 'v' it should
simply return the value of relkind. In the other cases I want "my" value.

But for some reason this returns the value of relkind for all rows. When I
remove the "else c.relkind" part, it works as expected.

My understanding of CASE is, that it will return the value of the first
expresion that evaluates to true. The ELSE part is only returned if all previous
expressions do not match.

When using it agains a simple test-table (int, char(1)), it's working as
expected. So this seems to be related to the pg_class table.

So what am I missing here?

I am using 8.2.5 on Windows XP

Thanks in advance
Thomas

> I wanted to use the following statement to "translate" the relkind
> column to a
> more descriptive value:
>
> select c.relname
>         case
>           when c.relkind in ('t','r') then 'table'
>           when c.relkind = 'i' then 'index'
>           when c.relkind = 'S' then 'sequence'
>           when c.relkind = 'v' then 'view'
>           else c.relkind
>         end as mykind
> from pg_class c
> ;
>
> The idea is that for anything else than 't', 'r', 'i', 'S' or 'v' it
> should
> simply return the value of relkind. In the other cases I want "my"
> value.
>
> But for some reason this returns the value of relkind for all rows.
> When I
> remove the "else c.relkind" part, it works as expected.


I agree, this seems confusing.  I found a section of the doc that caught my
eye:

"The data types of all the result expressions must be convertible to a
single output type."

Which led me to try this, which works:

select c.relname,
        case
          when c.relkind in ('t','r') then 'table'
          when c.relkind = 'i' then 'index'
          when c.relkind = 'S' then 'sequence'
          when c.relkind = 'v' then 'view'
          else c.relkind::text
        end as mykind
from pg_class c

On Sat, 1 Mar 2008, Thomas Kellerer wrote:

> I was writing a statement retrieve dependency information out of the
> system catalog, when I noticed something that I didn't expect.
>
> I wanted to use the following statement to "translate" the relkind
> column to a more descriptive value:
>
> select c.relname
>         case
>           when c.relkind in ('t','r') then 'table'
>           when c.relkind = 'i' then 'index'
>           when c.relkind = 'S' then 'sequence'
>           when c.relkind = 'v' then 'view'
>           else c.relkind
>         end as mykind
> from pg_class c
> ;
>
> The idea is that for anything else than 't', 'r', 'i', 'S' or 'v' it should
> simply return the value of relkind. In the other cases I want "my" value.
>
> But for some reason this returns the value of relkind for all rows. When I
> remove the "else c.relkind" part, it works as expected.

Actually, it doesn't exactly in my tests... for sequences it will
apparently return 's' not 'S'.

It looks like the problem is that relkind is of the somewhat odd
PostgreSQL type "char" not an actual char(1), so with the else in there it
appears to try to force the unknown literals into that type which only
takes the first character. It will probably work if you cast in the else,
like "else CAST(c.relkind as CHAR(1))".

> "The data types of all the result expressions must be convertible to a
> single output type."

The type of the field pg_class.relkind appears to be "char" which is
described in the notes as:

The type "char" (note the quotes) is different from char(1) in that it
only uses one byte of storage. It is internally used in the system catalogs
as a poor-man's enumeration type.

http://www.postgresql.org/docs/8.3/interactive/datatype-character.html

But one would expect "char" to be "convertible" to text for the purposes
of CASE.  Both implicit and explicit cast to text seems to work fine.

Adam Rich wrote on 01.03.2008 01:02:
>> "The data types of all the result expressions must be convertible to a
>> single output type."
>
> The type of the field pg_class.relkind appears to be "char" which is
> described in the notes as:
>
> The type "char" (note the quotes) is different from char(1) in that it
> only uses one byte of storage. It is internally used in the system catalogs
> as a poor-man's enumeration type.
>
> http://www.postgresql.org/docs/8.3/interactive/datatype-character.html
>
> But one would expect "char" to be "convertible" to text for the purposes
> of CASE.  Both implicit and explicit cast to text seems to work fine.

OK, I'm glad it boils down to a datatype issue ;)

My SQL frontend reported that column as char(1) so I didn't notice the subtle
difference (actually the JDBC driver returns char, not "char") and tested it
agains a table with (int, char(1)).

Using c.relkind::text works fine.

Thanks for all the answers

Thomas

Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> It looks like the problem is that relkind is of the somewhat odd
> PostgreSQL type "char" not an actual char(1), so with the else in there it
> appears to try to force the unknown literals into that type which only
> takes the first character. It will probably work if you cast in the else,
> like "else CAST(c.relkind as CHAR(1))".

Right, the problem is that all the string literals are "unknown" and
don't force a type decision, so the "char" type gets chosen as the
result type of the CASE, and then the literals get coerced to that.

If you explicitly cast any one of the CASE output expressions to text
--- either relkind, or any of the constants --- the behavior is as the
OP expects.

            regards, tom lane