Re: Column name 'user' not allowed?

Thomas Mueller <news-exp-dec04@tmueller.com> wrote:
> Hi,
>
> I tried to install phpopenchat but I can't create this table:
>
> poc=> CREATE TABLE poc_user_account (
> poc(>   USER varchar(255) NOT NULL,
> poc(>   PASSWORD varchar(255),
> poc(>   CONFIRM_CODE char(32),
> poc(>   DISABLED int NOT NULL DEFAULT '0',
> poc(>   KICKED int NOT NULL DEFAULT '0',
> poc(>   PASSWORD_NEW varchar(255),
> poc(>   PRIMARY KEY (USER)
> poc(> );
> ERROR:  syntax error at or near "USER" at character 35
>
> After searching a while what's wrong I renamed column USER to USER2 and
> now I can create the table! I don't see a reason why column name USER
> isn't allowed?!

Because it's a reserved word in PostgreSQL's SQL syntax.

You can also work around this by enclosing the name in quotes.  This also
makes the column name case-sensitive though, so you need to be sure that
_all_ processes/code/whatever that accesses this table can properly address
the column with the proper case.  i.e. if you use "USER" and later try to
SELECT user FROM poc_user_account, you'll get an error that the column
doesn't exist.  In fact, even if you do SELECT USER FROM poc_user_account,
you'll still get an error, as Postgres will fold the name to lower case.
Once you've got a case-sensitive name, you must do SELECT "USER" FROM ...

--
Bill Moran
Potential Technologies
http://www.potentialtech.com