Обсуждение: can't create user collumn

Hi,
i'm just starting with postgres DB, but this looks very strange to me:

If i try to create table with collumn user, it fails with error:
create exec error:ERROR:  syntax error at or near "user" at character 368

query:
CREATE TABLE Log (
log_date        date            NOT NULL,
log_time        time            NOT NULL,
timezone        int             NOT NULL default  60 ,
dest            varchar(20)     NOT NULL default 'messages',
hostname        varchar(100)    NOT NULL default 'localhost',
source          varchar(20)     NOT NULL default 'db_speed',
pid             numeric(10)     NOT NULL default  0 ,
category        int             NOT NULL default  10 ,
priority        int             NOT NULL default  10 ,
user            int             NOT NULL default  0 ,
log             varchar(800)    NOT NULL
);

But if I change the collumn name to "usr" (just this, nothing else), it
is o.k. and the table is created.

The question is, if I omit something, or it is a 'feature' of postgres,
or if it is a bug?

Thanks

Jiri Zouhar

p.s. I have postgres (PostgreSQL) 8.0.7, RH FC4 build

On 3/17/06, loki <loigu@centrum.cz> wrote:
> Hi,
> i'm just starting with postgres DB, but this looks very strange to me:
>
> If i try to create table with collumn user, it fails with error:
> create exec error:ERROR:  syntax error at or near "user" at character 368
>
> query:
> CREATE TABLE Log (
> log_date        date            NOT NULL,
> log_time        time            NOT NULL,
> timezone        int             NOT NULL default  60 ,
> dest            varchar(20)     NOT NULL default 'messages',
> hostname        varchar(100)    NOT NULL default 'localhost',
> source          varchar(20)     NOT NULL default 'db_speed',
> pid             numeric(10)     NOT NULL default  0 ,
> category        int             NOT NULL default  10 ,
> priority        int             NOT NULL default  10 ,
> user            int             NOT NULL default  0 ,
> log             varchar(800)    NOT NULL
> );
>
> But if I change the collumn name to "usr" (just this, nothing else), it
> is o.k. and the table is created.


user is a reserved sql word:

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

--
Postgresql & php tutorials
http://www.designmagick.com/

On Fri, 17 Mar 2006, loki wrote:

> Hi,
> i'm just starting with postgres DB, but this looks very strange to me:
>
> If i try to create table with collumn user, it fails with error:
> create exec error:ERROR:  syntax error at or near "user" at character 368

USER is a reserved word in SQL and as such cannot be used as a non-quoted
identifier so "user" (with the quotes) should work, however you'd probably
have to use the quotes in all cases. Technically, we allow some reserved
words as non-quoted identifiers in some places, however to be compliant to
spec you cannot use any of the reserved words that way.