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
signature.asc
Description: Digital signature
