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