From: | Joshua Tolley <eggyknap(at)gmail(dot)com> |
---|---|
To: | Piergiorgio Buongiovanni <piergiorgio(dot)buongiovanni(at)netspa(dot)it> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #5700: double-quoting column names |
Date: | 2010-10-08 14:14:19 |
Message-ID: | 4caf273e.08c88e0a.3edd.ffffd84a@mx.google.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Bryson Holland | 2010-10-08 23:13:06 | bug report |
Previous Message | Joshua Tolley | 2010-10-08 14:03:38 | Re: BUG #5698: pg_dump (8.3.11) does not consider constrains |