Red Hat Bugzilla – Bug 180407
Numeric columns fail in WHERE clause when using tablename qualifier
Last modified: 2013-07-02 23:07:34 EDT
Description of problem:
SQL query problem originally identified using PEAR DB_DataObject library;
however, library can be removed from equation and same error for valid SQL query
occurs utilizing command line tools. Latest version of PostgreSQL from
postgresql.org does not have this problem; however, fix is needed for enterprise
customers not willing to deviate and go off-distro.
Assuming the following table definition:
CREATE TABLE owners (
id bigserial NOT NULL,
"user" bigint NOT NULL,
"name" varchar(25) NOT NULL
The following query:
SELECT * FROM owners WHERE ( owners.user = 1 )
produces the following SQL error:
ERROR: syntax error at or near "user" at character 39
While the following query:
SELECT * FROM owners WHERE (user = 1)
does not. Also, queries against the varchar do not produce errors:
SELECT * FROM owners WHERE ( owners.name = 'steve' )
would not produce an error. Quotes around the number and removing/adding the
parentheses have no effect on the error message. The problem seems to stem from
the inclusion of the owners. qualifier on numeric types, not any other factor.
The inconsistent behavior of the string identified the problem.
Version-Release number of selected component (if applicable):
PostgreSQL 7.4.8 from distribution/updates has this problem.
PostgreSQL 8.1.2 (latest) built from SRPM does not
Steps to Reproduce:
See Description section above.
See error up in the description.
Should be considered valid query as is the case in latest PostgreSQL 8.1.2 from
Originally reported on PEAR web-site:
USER is a reserved word according to both the SQL standard and the Postgres grammar. You can use it as
a column name only if you always surround it in double quotes. The fact that the 8.1 grammar lets you
use it as the second name in a qualified identifier without quotes is a bit interesting, but it does not make
7.4's behavior a bug ... and in fact you'd be pretty foolish to assume that releases after 8.1 will still let you
do that. (The example without the qualified name isn't doing what you think at all ... it's invoking the
Bottom line: choose a different name for your column, or get used to using double quotes.