Syntax Error when using a case when conditional

Question

I am getting the following error:

postgresql error: syntax error at or near "2020" LINE 2: ... THEN amount * split_exchange_rate ELSE 0 END) AS 2020Q4_rev...

See code below. I am using postgresql

SELECT object_id
  , SUM(CASE WHEN created::date >= '2020-10-01' AND created::date <= '2020-12-31' THEN amount * exchange_rate ELSE 0 END) AS 2020Q4_revenue
  , SUM(CASE WHEN created::date >= '2021-01-01' AND created::date <= '2021-03-31' THEN amount * exchange_rate ELSE 0 END) AS 2021Q1_revenue
  , SUM(CASE WHEN created::date >= '2021-04-01' AND created::date <= '2021-06-30' THEN amount * exchange_rate ELSE 0 END) AS 2021Q2_revenue
  , SUM(CASE WHEN created::date >= '2021-07-01' AND created::date <= '2021-09-30' THEN amount * exchange_rate ELSE 0 END) AS 2021Q3_revenue
from raw_data.purchase_revenue pr
group by 1

The problem is not with the conditional at all, it's with `2020Q4_revenue` not being a valid identifier (to be used as a column alias). You [need to quote it](https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS) (if you insist on using that name). — Bergi, Oct 19 '21 at 02:31
Note that you can also use the `filter()` operator to do conditional aggregation: `sum(amount * exchange_rate) filter (where ...)` I find that easier to read (but you still need to use a valid SQL identifier for the column alias) — , Oct 19 '21 at 05:32

score 3 · Accepted Answer · answered Oct 19 '21 at 03:08

An alias like 2020Q4_revenue is an “identifier” in SQL, so it must conform with the rules:

SQL identifiers and key words must begin with a letter (a-z, but also letters with diacritical marks and non-Latin letters) or an underscore (_). Subsequent characters in an identifier or key word can be letters, underscores, digits (0-9), or dollar signs ($). Note that dollar signs are not allowed in identifiers according to the letter of the SQL standard, so their use might render applications less portable.

You can still use that alias name if you quote it with double quotes:

There is a second kind of identifier: the delimited identifier or quoted identifier. It is formed by enclosing an arbitrary sequence of characters in double-quotes ("). A delimited identifier is always an identifier, never a key word. So "select" could be used to refer to a column or table named “select”, whereas an unquoted select would be taken as a key word and would therefore provoke a parse error when used where a table or column name is expected.

Quoted identifiers can contain any character, except the character with code zero. (To include a double quote, write two double quotes.)

Syntax Error when using a case when conditional

1 Answers1