Confused about CASE

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