Thread: 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)
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" > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org] On Behalf Of > Thomas Mueller > 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 >
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 soI 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)