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

CodePudding user response：

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