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 (if you insist on using that name). — Bergi
– Bergi, Commented Oct 19, 2021 at 2: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) — user330315
– user330315, Commented Oct 19, 2021 at 5:32

Laurenz Albe · Accepted Answer · 2021-10-19 03:08:49Z

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.)

Collectives™ on Stack Overflow

Syntax Error when using a case when conditional

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related