Re: Question about WHERE CASE

Mike Martin <redtux1(at)gmail(dot)com> writes:
> I was always under the impression that a case expression could only be
> on the right side of a where expression ie:
> WHERE fieldname=<cse expression>
> Is this a postgres extention, cant find any documentation on this

SQL has always had two forms of CASE: you can do

CASE
WHEN boolean_expression1 THEN value1
[ WHEN boolean_expression2 THEN value2 ... ]
[ ELSE valueN ]
END

or you can do

CASE test_value
WHEN comparison_value1 THEN value1
[ WHEN comparison_value2 THEN value2 ... ]
[ ELSE valueN ]
END

The latter is effectively the same as

CASE
WHEN test_value = comparison_value1 THEN value1
[ WHEN test_value = comparison_value2 THEN value2 ... ]
[ ELSE valueN ]
END

except test_value is only supposed to be evaluated once.

This goes back at least as far as SQL-92.

It is documented, see
https://www.postgresql.org/docs/current/functions-conditional.html

regards, tom lane