Bug 180407 - Numeric columns fail in WHERE clause when using tablename qualifier
Summary: Numeric columns fail in WHERE clause when using tablename qualifier
Alias: None
Product: Red Hat Enterprise Linux 4
Classification: Red Hat
Component: postgresql   
(Show other bugs)
Version: 4.0
Hardware: All
OS: Linux
Target Milestone: ---
: ---
Assignee: Tom Lane
QA Contact: David Lawrence
URL: http://pear.php.net/bugs/bug.php?id=6694
Depends On:
TreeView+ depends on / blocked
Reported: 2006-02-07 22:06 UTC by Stephen Cuppett
Modified: 2013-07-03 03:07 UTC (History)
1 user (show)

Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of:
Last Closed: 2006-02-08 06:38:30 UTC
Type: ---
Regression: ---
Mount Type: ---
Documentation: ---
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---

Attachments (Terms of Use)

Description Stephen Cuppett 2006-02-07 22:06:00 UTC
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:

    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

How reproducible:


Steps to Reproduce:

See Description section above.
Actual results:

See error up in the description.

Expected results:

Should be considered valid query as is the case in latest PostgreSQL 8.1.2 from

Additional info:

Originally reported on PEAR web-site:


Comment 1 Tom Lane 2006-02-08 06:38:30 UTC
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 
CURRENT_USER function.)

Bottom line: choose a different name for your column, or get used to using double quotes.

Note You need to log in before you can comment on or make changes to this bug.