Thread: case-sensitivity inconsistency in quoted column aliases in FROM subselects
============================================================================ POSTGRESQL BUG REPORT TEMPLATE ============================================================================ Your name : Mike Hoolehan Your email address : mike@sycamorehq.com System Configuration --------------------- Architecture (example: Intel Pentium) : Intel Pentium Operating System (example: Linux 2.0.26 ELF) : RH 7.2, Kernel 2.4.9-13 PostgreSQL version (example: PostgreSQL-7.1.3): PostgreSQL-7.1.3 Compiler used (example: gcc 2.95.2) : gcc 2.96 (rpm version) Please enter a FULL description of your problem: ------------------------------------------------ if a quoted column alias in a FROM clause sub-select contains upper-case chars, then that column cannot be later referenced without using quotes Please describe a way to repeat the problem. Please try to provide a concise reproducible example, if at all possible: ---------------------------------------------------------------------- SELECT * FROM (SELECT col1 as "Foo" from table1) AS innerQuery WHERE Foo = 'whatever'; results in "ERROR: Attribute 'foo' not found" no matter what capitalization is used for "Foo" in the where clause (i.e. foo='whatever', FOO='whatever', etc). I assume this is a bug, since if the column is aliases as "foo" (with quotes, all lowercase), then the column can later be referenced without quotes with case-insensitivity. If you know how this problem might be fixed, list the solution below: ---------------------------------------------------------------------
Mike Hoolehan writes: > if a quoted column alias in a FROM clause sub-select contains upper-case > chars, then that column cannot be later referenced without using quotes This is expected behaviour. See also http://www.de.postgresql.org/users-lounge/docs/7.1/postgres/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS -- Peter Eisentraut peter_e@gmx.net
Mike Hoolehan <mike@sycamorehq.com> writes: > SELECT * FROM (SELECT col1 as "Foo" from table1) AS innerQuery > WHERE Foo = 'whatever'; > results in > "ERROR: Attribute 'foo' not found" > no matter what capitalization is used for "Foo" in the where clause > (i.e. foo='whatever', FOO='whatever', etc). > I assume this is a bug, since if the column is aliases as "foo" (with > quotes, all lowercase), then the column can later be referenced > without quotes with case-insensitivity. This is not a bug, it's the expected behavior. See the discussion of identifier case sensitivity in the manual, eg http://www.ca.postgresql.org/users-lounge/docs/7.1/postgres/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS regards, tom lane
On Wed, 9 Jan 2002, Mike Hoolehan wrote: > Please enter a FULL description of your problem: > ------------------------------------------------ > if a quoted column alias in a FROM clause sub-select contains upper-case > chars, then that column cannot be later referenced without using quotes > > Please describe a way to repeat the problem. Please try to provide a > concise reproducible example, if at all possible: > ---------------------------------------------------------------------- > SELECT * FROM (SELECT col1 as "Foo" from table1) AS innerQuery > WHERE Foo = 'whatever'; > results in > "ERROR: Attribute 'foo' not found" > no matter what capitalization is used for "Foo" in the where clause > (i.e. foo='whatever', FOO='whatever', etc). > I assume this is a bug, since if the column is aliases as "foo" (with > quotes, all lowercase), then the column can later be referenced > without quotes with case-insensitivity. Unquoted column and table names are folded to lower case (thus Foo is the same as foo or FOO or "foo", but "Foo" is different). In general, although "foo" will be matched, if you use quotes around the name in its creation you should always use quotes thereafter.