I tried to run the following query earlier at work and I'm got an error that I didn't understand.
I've never encountered it before as I don't think I ever tried writing a SELECT query with aggregates that returned a column named cost.
Here's the sample data & schema
-- setup & sample data
CREATE TABLE test (cost NUMERIC(10,4));
INSERT INTO test VALUES (123), (234), (345);
-- query
SELECT SUM(cost) cost FROM test;
-- error message:
ERROR: syntax error at or near "cost"
LINE 1: SELECT SUM(cost) cost FROM test;
^
Similarly, the following also return the same error:
SELECT FIRST_VALUE(cost) OVER () cost FROM test;
-- error message:
ERROR: syntax error at or near "cost"
LINE 1: SELECT FIRST_VALUE(cost) OVER () cost FROM test;
^
SELECT cost cost FROM test;
ERROR: syntax error at or near "cost"
LINE 1: SELECT cost cost FROM test;
^
However when I explicitly use AS to name the expression, there are no issues:
Example (these all work fine):
SELECT cost AS cost FROM test;
SELECT FIRST_VALUE(cost) OVER () AS cost FROM test;
SELECT SUM(cost) AS cost FROM test;
I basically gave the aggregate a different name and everything was fine, but I'm curious why this is happening? I imagine there is some name collision happening, but I'm not sure what it is colliding against.
Here's my postgresql version / platform information giving the above error:
PostgreSQL 10.4, compiled by Visual C++ build 1800, 64-bit / Windows 10.
Also, I ran it on the following system (AWS RDS) and got the same error
PostgreSQL 9.6.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit
