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

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?!

USER is a reserved word in SQL (at least in 92, and I think they only
added to the list in later versions) which technically makes it
unavailable as a non-quoted identifier. If you were careful about
quoting, you could use "USER" (note the double quotes) for creating it and
all references to it.

See:
http://www.postgresql.org/docs/current/static/sql-keywords-appendix.html

Try "UserName"

Try putting USER in quotes like this "USER".

-----Original Message-----
From: Thomas Mueller [mailto:news-exp-dec04@tmueller.com]
Sent: Wednesday, July 07, 2004 1:44 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Column name 'user' not allowed?

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?!

I am running Postgres 7.4.2 on Linux.

Thanks!

Thomas
--
http://www.tmueller.com for pgp key (95702B3B)

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

On Wed, Jul 07, 2004 at 05:17:08PM -0400, Bill Moran wrote:

Thomas Mueller <news-exp-dec04@tmueller.com> wrote:

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.

Even worse, you don't get an error at all, but you get your current connection
username:

alberto=# select user from pg_database;
current_user
--------------
alberto
alberto
alberto
(3 rows)

--
-----------------------
Alberto Cabello S�nchez
alberto@unex.es
Servicio de Inform�tica
924 289 351
-----------------------

On Thu, 8 Jul 2004, Alberto Cabello Sanchez wrote:

On Wed, Jul 07, 2004 at 05:17:08PM -0400, Bill Moran wrote:

Thomas Mueller <news-exp-dec04@tmueller.com> wrote:

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.

Even worse, you don't get an error at all, but you get your current connection
username:

alberto=# select user from pg_database;
current_user
--------------
alberto
alberto
alberto
(3 rows)

Right, because USER effectively means CURRENT_USER (as per the rules in
SQL92 6.2/SQL99 6.3). The choice of having USER be a reserved word which
basically means the same thing as CURRENT_USER by the committee doing the
SQL spec was unfortunate.

I went through the frustration of that as well. I guess all people making libraries which use RDBMs should read the SQL spec (like that'll happen) so they don't use reserved words.

I changed all my use of the word user to 'usr'. Shortness is next to godliness. I made sure to change ALL occurrences so I didn't have to remeber to where I had changed.
Duane Lee - EGOVX <DLee@mail.maricopa.gov> wrote:

<wrote ---------------------------------------------------------->
"'Thomas Mueller'" <news-exp-dec04@tmueller.com>, pgsql-general@postgresql.org

Try putting USER in quotes like this "USER".

-----Original Message-----
From: Thomas Mueller [mailto:news-exp-dec04@tmueller.com]
Sent: Wednesday, July 07, 2004 1:44 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Column name 'user' not allowed?

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?!

I am running Postgres 7.4.2 on Linux.

Thanks!
</quote ----------------------------------------------------->

On Thu, 2004-07-08 at 09:35, Stephan Szabo wrote:

On Thu, 8 Jul 2004, Alberto Cabello Sanchez wrote:

On Wed, Jul 07, 2004 at 05:17:08PM -0400, Bill Moran wrote:

Thomas Mueller <news-exp-dec04@tmueller.com> wrote:

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.

Even worse, you don't get an error at all, but you get your current connection
username:

alberto=# select user from pg_database;
current_user
--------------
alberto
alberto
alberto
(3 rows)

Right, because USER effectively means CURRENT_USER (as per the rules in
SQL92 6.2/SQL99 6.3). The choice of having USER be a reserved word which
basically means the same thing as CURRENT_USER by the committee doing the
SQL spec was unfortunate.

That said, the choice of USER as a column name in phpopenchat is even
more unfortunate. Someone should open a bug report with them and site
the sql spec so that they change the column to a friendlier (and more
compliant) name.

Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

I tried to install phpopenchat but I can't create this table:

poc=> CREATE TABLE poc_user_account (
poc(> USER varchar(255) NOT NULL,

Thanks everyone!
Creating the database with quoted USER column worked fine, now I have to
resolve the other problems.

I'd like to see one application developed for MySQL that works with any
other database out of the box ...

Thomas
--
http://www.tmueller.com for pgp key (95702B3B)