Обсуждение: BUG #5700: double-quoting column names

The following bug has been logged online:

Bug reference:      5700
Logged by:          Piergiorgio Buongiovanni
Email address:      piergiorgio.buongiovanni@netspa.it
PostgreSQL version: 8.4.3
Operating system:   Linux Redhat
Description:        double-quoting column names
Details:

Dear all,

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');

If you try to execute the following statement instead of the previous one:

INSERT INTO erc.TestTable( "name" ) VALUES ('Test');

your SQL statement works well.
As you can see the difference is in the column name: "Name" versus "name".
Moreover if you try to execute the following statement:

INSERT INTO erc.TestTable( Name ) VALUES ('Test');

the query returns successfully and the row is inserted in the database.
As you can see the only difference between the first statement and the last
one is the use of "" to quote the column name.
Can you explain why the DB Engine has this behaviour if you use "" to quote
the column name? We always use the quote_indent function when we prepare a
SQL statement (string) for execution and this strange behaviour causes an
error when we execute the statement.

Regards

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.
>=20
> If you define a table as follows:
>=20
> create table erc.TestTable ( Name varchar );
>=20
> and then execute the following statement:
>=20
> INSERT INTO erc.TestTable( "Name" ) VALUES ('Test');
>=20
> you obtain the following error:
>=20
> 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 create=
d 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