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