`user` name as alias for table does not work-CodePudding

So I have this simple query written in Postgres:

SELECT cp.contest_id, cp.username, user.rating FROM contest_participants cp 
  JOIN users user ON user.username = cp.username WHERE contest_id = '?';

and I keep getting this error:

ERROR:  syntax error at or near "."
LINE 1: SELECT cp.contest_id, cp.username, user.rating FROM contest_...

If I change the user table alias name, it's working like a charm but is bothering me not to know why exactly is that. Is there a problem with the alias name or it can be something else with the entire schema structure from the database?

As far as I can tell, the query is isolated to the selection I made, right?

CodePudding user response：

In Postgres SQL, user is a reserved keyword and cannot be used as an alias or other database object name, see the documentation here.

For an immediate fix, you may escape the alias in double quotes:

SELECT cp.contest_id, cp.username, user.rating
FROM contest_participants cp 
JOIN users "user" ON "user".username = cp.username
WHERE contest_id = '?';

Or, you could just an alias like usr, which gets across the same meaning without requiring escaping.