Thread: Possible error in psql or Postgres?

<small><font>Is this normal Postgres / psql behavior?<br /><br /> griffindb=# \d system.user;<br />
                                 Table "system.user"<br />   Column   |         Type          |                    
Modifiers<br/><br /> -----------+-----------------------+--------------------------------------------<br /> --------<br
/> username  | character varying(20) | not null<br />  password  | character varying(32) | not null<br />  firstname |
charactervarying(40) | not null default 'nema ime'::character vary<br /> ing<br />  lastname  | character varying(40) |
notnull default 'nema prezime'::character<br /> varying<br /> Indexes:<br />     "SystemUser_PK" PRIMARY KEY, btree
(username)CLUSTER<br /><br /> normal query: <br /><br /> griffindb=# select * from system.user where username =
'root';<br/>  username |             password             | firstname |   lastname<br />
----------+----------------------------------+-----------+---------------<br/>  root     |
1e7db545fccbf4e03abc6b71d329ab4f| Super     | administrator<br /> (1 row)<br /><br /> error query:<br /><br />
griffindb=#select * from system.user where user = 'root';<br />  username | password | firstname | lastname<br />
----------+----------+-----------+----------<br/> (0 rows)<br /><br /> column user does not exist should throw an
error!<br/><br /> PostgreSQL / psql version: 9.1.3 on Windows 7 64-bit<br /><br /> Should Postgres or psql report an
errorbecause column used in WHERE clause does not exist? </font><br /></small> 

On Tue, Jun 12, 2012 at 9:21 PM, Dusan Misic <promisic@gmail.com> wrote:
> Is this normal Postgres / psql behavior?
>
> griffindb=# \d system.user;
>                                   Table "system.user"
>   Column   |         Type          |                     Modifiers
>
> -----------+-----------------------+--------------------------------------------
> --------
>  username  | character varying(20) | not null
>  password  | character varying(32) | not null
>  firstname | character varying(40) | not null default 'nema ime'::character
> vary
> ing
>  lastname  | character varying(40) | not null default 'nema
> prezime'::character
> varying
> Indexes:
>     "SystemUser_PK" PRIMARY KEY, btree (username) CLUSTER
>
> normal query:
>
> griffindb=# select * from system.user where username = 'root';
>  username |             password             | firstname |   lastname
> ----------+----------------------------------+-----------+---------------
>  root     | 1e7db545fccbf4e03abc6b71d329ab4f | Super     | administrator
> (1 row)
>
> error query:
>
> griffindb=# select * from system.user where user = 'root';
>  username | password | firstname | lastname
> ----------+----------+-----------+----------
> (0 rows)
>
> column user does not exist should throw an error!
>
> PostgreSQL / psql version: 9.1.3 on Windows 7 64-bit
>
> Should Postgres or psql report an error because column used in WHERE clause
> does not exist?

User is not a column, it's a variable:
postgres=# select user;current_user
--------------mha
(1 row)


So it's comparing the constant to whatever your are logged in as. If
you log in as "root" in the database, it will return all rows in the
user table.

So in short, yes,  it's normal.

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Is this normal Postgres / psql behavior?

griffindb=# \d system.user;
                                  Table "system.user"
  Column   |         Type          |                     Modifiers

-----------+-----------------------+--------------------------------------------
--------
 username  | character varying(20) | not null
 password  | character varying(32) | not null
 firstname | character varying(40) | not null default 'nema ime'::character vary
ing
 lastname  | character varying(40) | not null default 'nema prezime'::character
varying
Indexes:
    "SystemUser_PK" PRIMARY KEY, btree (username) CLUSTER

normal query:

griffindb=# select * from system.user where username = 'root';
 username |             password             | firstname |   lastname
----------+----------------------------------+-----------+---------------
 root     | 1e7db545fccbf4e03abc6b71d329ab4f | Super     | administrator
(1 row)

error query:

griffindb=# select * from system.user where user = 'root';
 username | password | firstname | lastname
----------+----------+-----------+----------
(0 rows)

column user does not exist should throw an error!

PostgreSQL / psql version: 9.1.3 on Windows 7 64-bit

Should Postgres or psql report an error because column used in WHERE clause does not exist? 

On Jun 12, 2012, at 15:21, Dusan Misic <promisic@gmail.com> wrote:

Is this normal Postgres / psql behavior?

griffindb=# \d system.user;
                                  Table "system.user"
  Column   |         Type          |                     Modifiers

-----------+-----------------------+--------------------------------------------
--------
 username  | character varying(20) | not null
 password  | character varying(32) | not null
 firstname | character varying(40) | not null default 'nema ime'::character vary
ing
 lastname  | character varying(40) | not null default 'nema prezime'::character
varying
Indexes:
    "SystemUser_PK" PRIMARY KEY, btree (username) CLUSTER

normal query:

griffindb=# select * from system.user where username = 'root';
 username |             password             | firstname |   lastname
----------+----------------------------------+-----------+---------------
 root     | 1e7db545fccbf4e03abc6b71d329ab4f | Super     | administrator
(1 row)

error query:

griffindb=# select * from system.user where user = 'root';
 username | password | firstname | lastname
----------+----------+-----------+----------
(0 rows)

column user does not exist should throw an error!

PostgreSQL / psql version: 9.1.3 on Windows 7 64-bit

Should Postgres or psql report an error because column used in WHERE clause does not exist? 

http://www.postgresql.org/docs/9.0/interactive/functions-info.html

"user" is actually a function the returns the current_user.  It is an SQL special function and thus does not require the use of () after the function name.  So basically you are saying "where current_user = 'root'" which is either a constant true or false for the statement.

David J.