August 11th, 2003, 11:57 PM
PostgreSQL allergic to "user"?
I'm a relative beginner with PostgreSQL, and tonight I have been dealing with a mystical problem debugging a totally trivial Hibernate demo application I was putting together... eventually I traced the bug to an SQL fragment being fed to the database:
SELECT user.uid as uid, user.email as email, user.last_login as last_login FROM USERS user;
It turns out that the parser barfs every time it is asked to "select" something that starts with "user.", returning the rather unhelpful message
ERROR: parser: parse error at or near "." at character 12
I experimented with this for a while, and even nonsensical stuff like "select user.userid from foo" returns a parser error "at character 12"...
Not only that, but "select something from user;" comes back with a "parse error at or near "user" at character 17", and not a complaint that relation user doesn't even exist!
I would like to have some insight into this, because it just seems so stupid... a lot of applications will most definitely want to have an entity called "user", so I would assume either a lot of people have seen this before or I just have something pretty fundamentally wrong here!
Postgresql version is 7.3.2.
August 12th, 2003, 12:02 AM
After some more research...
Turns out that postgresql complains about the use of a reserved word (such as "user") even if that reserved word is used in a place where it syntactically speaking could exist quite happily... a bit silly, but I guess one just has to live with it?
August 12th, 2003, 10:29 AM
If you have to use Postgres reserved words enclose them in quotes. So you'd do SELECT "user".fieldname FROM "user" WHERE "user".fieldname2 = 'blah'
BTW, use quotes also if you're using tables that aren't all in lowercase.
Last edited by bcyde; August 12th, 2003 at 10:32 AM.
PostgreSQL, it's what's for dinner...