Re: Confused about CASE

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))".