Referencing subquery columns in PostgreSQL

Question

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!

score 3 · Accepted Answer · 2014-02-12 18:29:20Z

3

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

edited Feb 12, 2014 at 18:29

answered Feb 12, 2014 at 18:22

user330315

Sign up to request clarification or add additional context in comments.

6 Comments

JM4 Over a year ago

tried that too. I should have mentioned in my OP- I'll update because the error is different but thrown nonetheless

user330315 Over a year ago

@JM4: see my edit. Your identifer "minCounter" is not the same as the reference in the outer query named minCounter

JM4 Over a year ago

Thanks @a_horse_with_no_name - I'm now running into GROUP BY errors from the other joins but I can take elsewhere or figure out: ERROR: column "c.mincounter" must appear in the GROUP BY clause or be used in an aggregate function LINE 4: q.minCounter

user330315 Over a year ago

@JM4: and again you fall into a trap with MySQL ignoring the SQL standard and implementing group by incorrectly: mysqlperformanceblog.com/2006/09/06/…

JM4 Over a year ago

Sounds like I need to relearn proper SQL and tell MySQL to go bite a nut.

|

Collectives™ on Stack Overflow

Referencing subquery columns in PostgreSQL

Accounting for literal versus named columns

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Accounting for literal versus named columns

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related