CODEWITHSUNDEEP
postgresql
JM4
JM4

Reputation: 6788

Referencing subquery columns in PostgreSQL

I am very familiar with MySQL syntax but am seemingly running into some growing pains moving into a PostgreSQL environment. In MySQL you can reference columns in joined subqueries easily but I am thrown errors in PostgreSQL.

For example:

SELECT
  a.userID,
  b.firstname,
  c.minCounter
FROM users a
JOIN users_info b ON (a.userID = b.userID)
LEFT JOIN (
   SELECT
      user_id,
      COUNT(*) AS 'minCounter'
   FROM sales
   WHERE
      total_amount > 500
   GROUP BY user_id
) as c ON (b.userID = c.user_id)

referencing the subquery "c" and its temp column "minCounter" has no issues. In PostgreSQL however, a similar query would return the error:

ERROR:  syntax error at or near "'minCounter'"
LINE 10:   COUNT(*) AS 'minCounter'

Accounting for literal versus named columns

If I switch Line 10 to state: COUNT(*) AS "minCounter"

my error simply adjusts to:

ERROR:  column c.mincounter does not exist
LINE 4: c.minCounter

Any suggestions as to what syntax or general naming difference there are that I'm missing?

(Note, I am not sure what version of PostgreSQL we are running because it is obfuscated by our external dev team).

Thanks!

Upvotes: 0

Views: 1624

Answers (1)

user330315
user330315

Reputation:

This has nothing to do with referencing a column but with providing the alias for a column.

'minCounter' is not a column alias it's character literal. If you want to quote an identifier you have to use double quotes: "minCounter"

See the manual for details: http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

This works as specified and required by the SQL standard (which MySQL chose to ignore and does not really distinguish between character literals using single quotes and object identifiers using double quotes).

Once you quote an identifier using double quotes, it becomes case-sensitive. So in your outer query you also need "minCounter" because minCounter is a different name (this is again according to the SQL standard which - you guessed - is also ignored by MySQL)

To sum it up:

  • no quotes: a case insensitive identifier (or keyword, or operator, ...)
  • double quotes: a case sensitive identifier
  • single quotes: a constant character value (aka "character literal")

In general I'd strongly recommend to never quote any identifier

Upvotes: 3

Related Questions