Re: Possible error in psql or Postgres?

On Tue, Jun 12, 2012 at 9:21 PM, Dusan Misic <promisic(at)gmail(dot)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/