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?
>Solution :
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.