PostgreSQL CASE statement

Question

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?

Erwin Brandstetter · Accepted Answer · 2016-04-17 12:17:59Z

3

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.

edited Apr 17, 2016 at 12:17

answered Apr 17, 2016 at 0:29

Erwin Brandstetter

668k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Pavel Stehule Over a year ago

Modern SQL allows list of expression in SQL CASE too. The procedural CASE is based on modern standard, SQL CASE is still ANSI SQL 99 compliant (missing optional feature F263). The reason, why SQL CASE missing this feature is simple - no body wrote it for Postgres.

Erwin Brandstetter Over a year ago

@PavelStehule: Oh, interesting, this might be low hanging froot for an ambitious developer.

Collectives™ on Stack Overflow

PostgreSQL CASE statement

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related