Case clause doesn't report syntactic error
Dear friends!
I am reporting this behavior when using case clause(let's say interactively):
Version: PostgreSQL 12.17 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-18), 64-bit
Maybe it's fixed(if it needed fixing) going forward.
This generates no error:
select case '4'::int when 1 then 1 when 4 then 11 else 2 end;
(prints 11)
It doesn't match the documented format:
CASE WHEN condition THEN result
[WHEN ...]
[ELSE result]
END
As long as the type of entry between CASE and first WHEN matches type of WHEN conditions no syntax error is being generated!
But the whole CASE maybe isn't operating as expected.
Example of when error is produced:
select case 't' when 1 then 1 else 2 end;
ERROR: operator does not exist: text = integer
LINE 1: select case 't' when 1 then 1 else 2 end;
^
HINT: No operator matches the given name and argument types. You might need to add explicit type casts.
Please tell me if this is an undocumented feature or a bug.
Regards,
Leo Volin
SSA
Intelligent Audit
[breast cancer awareness logo]<https://www.intelligentaudit.com/>
o: (201) 880.1110 ext. 103
w: www.intelligentaudit.com<http://www.intelligentaudit.com/>
Attachments:
On Saturday, September 14, 2024, Leo Volin <lvolin@intelligentaudit.com>
wrote:
It doesn't match the documented format:
CASE WHEN *condition* THEN *result*
[WHEN ...]
[ELSE *result*]
END
As long as the type of entry between CASE and first WHEN matches type of
WHEN conditions no syntax error is being generated!
Read the entire section documenting case.
https://www.postgresql.org/docs/current/functions-conditional.html#FUNCTIONS-CASE
There are two forms documented - the general and then the “simple”
variant. You example demonstrates the later.
David J.
Show quoted text
Leo Volin <lvolin@intelligentaudit.com> writes:
This generates no error:
select case '4'::int when 1 then 1 when 4 then 11 else 2 end;
(prints 11)
It doesn't match the documented format:
CASE WHEN condition THEN result
[WHEN ...]
[ELSE result]
END
You need to read down a couple of paragraphs to where the
"simple" variant of CASE is explained [1]https://www.postgresql.org/docs/current/functions-conditional.html#FUNCTIONS-CASE:
CASE expression
WHEN value THEN result
[WHEN ...]
[ELSE result]
END
Perhaps we should rearrange that page so both syntax variants
appear at the top; but it's not wrong.
regards, tom lane
[1]: https://www.postgresql.org/docs/current/functions-conditional.html#FUNCTIONS-CASE