Re: Column name 'user' not allowed?

Thomas Mueller <news-exp-dec04(at)tmueller(dot)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