Обсуждение: BUG #5620: PostgreSQL won't accept the word "user" as a valid column name

The following bug has been logged online:

Bug reference:      5620
Logged by:          Samuel Marinov
Email address:      samuel.marinov@gmail.com
PostgreSQL version: 9.0b4
Operating system:   Ubuntu Linux 10.04 64-bit
Description:        PostgreSQL won't accept the word "user" as a valid
column name
Details:

I haven't tried this bug with any other platform or version. I installed
PostgreSQL from source, using the configure command: "./configure
--prefix=/usr/local/pgsql". I also started the server after the install with
the following command: "/usr/local/pgsql/bin/pg_ctl start -D
/usr/local/pgsql/data" while logged in as a non-root user. Also, I didn't
try creating a test database and user as the INSTALL file says, but creating
databases and users works fine anyway. Other than that, I used all the
defaults found in the INSTALL file for installing and starting PostgreSQL.
After that, I set a password for the postgres user (not the system postgres
user, but the database user) and created another user called ftp. I also
gave the ftp user full ownership to a database named ftp. Then, I logged in
as the ftp user, and tried to run the following query: "create table ftp
(user varchar, password varchar);". I got the following error message:

ERROR:  syntax error at or near "user" at character 19
STATEMENT:  create table ftp (user varchar, password varchar);
ERROR:  syntax error at or near "user"
LINE 1: create table ftp (user varchar, password varchar);

I tried using the name "username" for the first column, and it worked
perfectly.

On 08/14/2010 02:14 AM, Samuel Marinov wrote:
>
> The following bug has been logged online:
>
> Bug reference:      5620
> Logged by:          Samuel Marinov
> Email address:      samuel.marinov@gmail.com
> PostgreSQL version: 9.0b4
> Operating system:   Ubuntu Linux 10.04 64-bit
> Description:        PostgreSQL won't accept the word "user" as a valid
> column name
> Details:
>
> I haven't tried this bug with any other platform or version. I installed
> PostgreSQL from source, using the configure command: "./configure
> --prefix=/usr/local/pgsql". I also started the server after the install with
> the following command: "/usr/local/pgsql/bin/pg_ctl start -D
> /usr/local/pgsql/data" while logged in as a non-root user. Also, I didn't
> try creating a test database and user as the INSTALL file says, but creating
> databases and users works fine anyway. Other than that, I used all the
> defaults found in the INSTALL file for installing and starting PostgreSQL.
> After that, I set a password for the postgres user (not the system postgres
> user, but the database user) and created another user called ftp. I also
> gave the ftp user full ownership to a database named ftp. Then, I logged in
> as the ftp user, and tried to run the following query: "create table ftp
> (user varchar, password varchar);". I got the following error message:
>
> ERROR:  syntax error at or near "user" at character 19
> STATEMENT:  create table ftp (user varchar, password varchar);
> ERROR:  syntax error at or near "user"
> LINE 1: create table ftp (user varchar, password varchar);
>
> I tried using the name "username" for the first column, and it worked
> perfectly.

not sure why you consider this a bug - but "user" is a reserved word in
PostgreSQL and the SQL-Standard (see
http://www.postgresql.org/docs/9.0/static/sql-keywords-appendix.html) so
you would have to quote the keyword like:

create table ftp ("user" varchar, password varchar);

in your code - but I would advise against using reserved words at all
because you will end up having to quote them all over the place in each
and every query.


Stefan

Stefan Kaltenbrunner <stefan@kaltenbrunner.cc> writes:
> On 08/14/2010 02:14 AM, Samuel Marinov wrote:
>> ERROR:  syntax error at or near "user"
>> LINE 1: create table ftp (user varchar, password varchar);

> not sure why you consider this a bug - but "user" is a reserved word in
> PostgreSQL and the SQL-Standard (see
> http://www.postgresql.org/docs/9.0/static/sql-keywords-appendix.html) so
> you would have to quote the keyword like:

> create table ftp ("user" varchar, password varchar);

> in your code - but I would advise against using reserved words at all
> because you will end up having to quote them all over the place in each
> and every query.

Yeah.  The reason it's reserved is that per spec, USER is equivalent to
CURRENT_USER, ie it's a built-in function that returns the current login
name.  So anytime you forgot and wrote
    select user from ...
instead of
    select "user" from ...
it would still work and you'd get mysteriously wrong output.  You
don't want to go there.

            regards, tom lane