clay clay - 7 months ago 41
SQL Question

PostgreSQL CASE statement

This is from the official documentation (http://www.postgresql.org/docs/9.4/static/plpgsql-control-structures.html)


and two forms of CASE:

CASE ... WHEN ... THEN ... ELSE ... END CASE


CASE WHEN ... THEN ... ELSE ... END CASE



This doesn't work:

select case when 1 < 2 then 'a' else 'b' end case from pg_database limit 1;


It works with
end
instead of
end case
, though:

select case when 1 < 2 then 'a' else 'b' end from pg_database limit 1;


This is with PostgreSQL 9.4.6.

Why doesn't the syntax in the official docs match the syntax that the server apparently requires?

Answer

You are confusing documentation for PL/pgSQL with the one for SQL. You link to the manual for PL/pgSQL, but your code displays an SQL expression.

Both use the key word CASE, but it's not the same thing. CASE in PL/pgSQL is a control structure for the procedural language, while CASE in SQL is a conditional expression.

You are not the first to be confused. There are other minor differences in the syntax. For instance, PL/pgSQL allows a list of values for the "simple CASE" variant, which is not possible in SQL:

... WHEN expression [, expression [ ... ]] THEN

Would be nice to have in SQL as well, but the standard does allow it and Postgres sticks to the standard.