CODEWITHSUNDEEP
sqlpostgresqlcaseplpgsql
clay
clay

Reputation: 20370

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?

Upvotes: 1

Views: 1451

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656381

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 doesn't allow it and Postgres sticks to the standard.

Upvotes: 3

Related Questions