This is because user is a "reserved" word.
user is a macro for the function current_user()
Try this SQL: SELECT user;

It is best to change the table name from user to username;

However it is probably a wise idea for Postgres to emit some sort of error
on the create mentioning the reserved word.

At 11:23 AM 12/28/01 -0500, you wrote:
Avi Kivity (avi@argo.co.il) reports a bug with a severity of 3
The lower the number the more severe it is.

Short Description
Keyword USER not detected in CREATE VIEW

Long Description
The reserved keyword USER is rejected in CREATE TABLE, as expected:

avi=> create table x (user varchar);
ERROR: parser: parse error at or near "user"

In my ignorance, however, I tried to create a view with a field named user:

avi=> create table y (x varchar);
CREATE
avi=> create view z as select x as user from y;
CREATE

Note no error.

avi=> \d z
View "z"
Attribute | Type | Modifier
-----------+-------------------+----------
user | character varying |
View definition: SELECT y.x AS "user" FROM y;

avi=> insert into y values ('zz');
INSERT 43771 1
avi=> select user from z;
current_user
--------------
avi
(1 row)

Wrong value returned.

avi=> select "user" from z;
user
------
zz
(1 row)

Correct value returned.

My expected result: an error message in CREATE VIEW


Sample Code


No file was uploaded with this report


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Search Discussions

  • Darcy Buskermolen at Dec 28, 2001 at 4:41 pm
    This is because user is a "reserved" word.
    user is a macro for the function current_user()
    Try this SQL: SELECT user;

    It is best to change the table name from user to username;

    However it is probably a wise idea for Postgres to emit some sort of error
    on the create mentioning the reserved word.

    At 11:23 AM 12/28/01 -0500, you wrote:
    Avi Kivity (avi@argo.co.il) reports a bug with a severity of 3
    The lower the number the more severe it is.

    Short Description
    Keyword USER not detected in CREATE VIEW

    Long Description
    The reserved keyword USER is rejected in CREATE TABLE, as expected:

    avi=> create table x (user varchar);
    ERROR: parser: parse error at or near "user"

    In my ignorance, however, I tried to create a view with a field named user:

    avi=> create table y (x varchar);
    CREATE
    avi=> create view z as select x as user from y;
    CREATE

    Note no error.

    avi=> \d z
    View "z"
    Attribute | Type | Modifier
    -----------+-------------------+----------
    user | character varying |
    View definition: SELECT y.x AS "user" FROM y;

    avi=> insert into y values ('zz');
    INSERT 43771 1
    avi=> select user from z;
    current_user
    --------------
    avi
    (1 row)

    Wrong value returned.

    avi=> select "user" from z;
    user
    ------
    zz
    (1 row)

    Correct value returned.

    My expected result: an error message in CREATE VIEW


    Sample Code


    No file was uploaded with this report


    ---------------------------(end of broadcast)---------------------------
    TIP 3: if posting/reading through Usenet, please send an appropriate
    subscribe-nomail command to majordomo@postgresql.org so that your
    message can get through to the mailing list cleanly
  • Tom Lane at Dec 28, 2001 at 4:49 pm

    pgsql-bugs@postgresql.org writes:
    avi=> create view z as select x as user from y;
    My expected result: an error message in CREATE VIEW
    This is not a bug, it is intentional behavior. "AS" names are not
    restricted to be non-reserved words.

    regards, tom lane
  • Thomas Lockhart at Dec 28, 2001 at 9:08 pm

    avi=> create view z as select x as user from y;
    My expected result: an error message in CREATE VIEW
    This is not a bug, it is intentional behavior. "AS" names are not
    restricted to be non-reserved words.
    Sure, but his point is that the resulting view has a column which is
    named "user" and which is not directly accessible without using double
    quotes.

    I'm not sure that would qualify as a bug, but certainly is a gotcha...

    - Thomas
  • Tom Lane at Dec 28, 2001 at 9:17 pm

    Thomas Lockhart writes:
    avi=> create view z as select x as user from y;
    My expected result: an error message in CREATE VIEW
    This is not a bug, it is intentional behavior. "AS" names are not
    restricted to be non-reserved words.
    Sure, but his point is that the resulting view has a column which is
    named "user" and which is not directly accessible without using double
    quotes.
    Yeah, but what would you do about it? The identical situation can be
    created in at least three ways:

    create table foo ("user" varchar);

    create view foo as select x as "user" from y;

    create view foo as select x as user from y;

    I would certainly object to emitting a warning notice in the first two
    cases. There might be some merit in warning about the third, but I don't
    see a reasonable way to distinguish it from the others.

    A not-unrelated point: would you also have the system warn about

    create table foo ("Foo" varchar);

    Here again the column cannot be named without using quotes. But I'd
    see issuing a warning about it as unwarranted nannyism.

    regards, tom lane
  • Thomas Lockhart at Dec 28, 2001 at 9:40 pm
    ...
    Here again the column cannot be named without using quotes. But I'd
    see issuing a warning about it as unwarranted nannyism.
    Yup. I'm not suggesting a change, but the more explicit discussion on
    *why* this may be acceptable is probably helpful. I think we are pretty
    close in 'tude on the nannyism issues; I like building bigger guns too
    ;)

    - Thomas

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-bugs @
categoriespostgresql
postedDec 28, '01 at 4:23p
activeDec 28, '01 at 9:40p
posts6
users4
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase