Possible error in psql or Postgres?

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/

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.

On 12.6.2012 21:33, David Johnston wrote:

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.

Found the problem. Work USER is a reserved word. It is written in
PostgreSQL documentation. Sorry for false alarm. My bad.