Re: [HACKERS] Possible error in psql or Postgres?
On 12.6.2012 21:33, David Johnston wrote: On Jun 12, 2012, at 15:21, Dusan Misic 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.
Re: [HACKERS] Possible error in psql or Postgres?
On Jun 12, 2012, at 15:21, Dusan Misic 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.
Re: [HACKERS] Possible error in psql or Postgres?
On Tue, Jun 12, 2012 at 9:21 PM, Dusan Misic 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/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Possible error in psql or Postgres?
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?
