Re: BUG #5700: double-quoting column names

On Fri, Oct 08, 2010 at 12:20:35PM +0000, Piergiorgio Buongiovanni wrote:
> recently we experienced a strange behaviour with double quoting of column
> names in UPDATE statements.
>
> If you define a table as follows:
>
> create table erc.TestTable ( Name varchar );
>
> and then execute the following statement:
>
> INSERT INTO erc.TestTable( "Name" ) VALUES ('Test');
>
> you obtain the following error:
>
> ERROR: column "Name" of relation "TestTable" does not exist
> LINE 1: INSERT INTO erc.TestTable( "Name" ) VALUES ('Test');

The problem is you didn't double-quote the field name when you created the
table, so the column name was created in lowercase. From the documentation:
"Quoting an identifier also makes it case-sensitive, whereas unquoted names
are always folded to lower case." [1] So your CREATE TABLE statement created a
table called "erc.testtable" with a column called "name", and when you tried
to insert into a column called "Name", it failed.

I wonder, though, if you've shown us exactly what you did or not; in my
systems, the table name in the error message would all be lowercase. In any
event, as a rule, if you're going to quote your identifiers once, you need to
quote them all the time. For that reason, many people choose to stick with all
lowercase table and column names.

[1] http://www.postgresql.org/docs/9.0/interactive/sql-syntax-lexical.html

--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com