Richard,
thanks for the great explanation. I'm sorry because I missed your notes to performance
in previous e-mail between the lines.
Cast (in)sensitivity is much more clear for me now.
When I moved project from MSSQL to Postgres I did not create tables manually,
I used some mssql-to-postgres migration tool. So it looks
like this tool used to put double-quotes when creating table.
So it all depends on how table is exactly created, thank you.
A last question - is there any way how to 'switch' this for
table without re-creating table again ?
Thanks in advance.
S pozdravom / Best regards,
Jozef Ševčík
sevcik@styxsystems.com
+420 608 782 813
-----Original Message-----
From: Richard Huxton [mailto:dev@archonet.com]
Sent: Wednesday, April 09, 2008 6:57 PM
To: Jozef Ševčík
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Quoting table/column names vs performance
Jozef Ševčík wrote:
> Richard,
>
> thanks for the answer.
> In fact, I double-quoted identifiers only because PgSQL forced me to do so
> when using capitalized letters in table/column name.
Well, if you don't quote them they get folded to lower-case and you get
case-insensitive matching.
CREATE TABLE MyTable1 (a int); -- ends up as mytable1
CREATE TABLE "MyTable2" (a int); -- stays as MyTable2
SELECT * FROM MyTable1; -- OK, looks for "mytable1"
SELECT * FROM MYTABLE1; -- also OK
SELECT * FROM MyTaBlE1; -- also OK
SELECT * FROM "MyTable1";-- Fails, looks for "MyTable1"
SELECT * FROM MyTable2; -- Fails, looks for "mytable2"
SELECT * FROM "MyTable2"; -- OK
> I'm OK with this if it's PgSQL requirement (app runs on NHibernate so I just change
> column="MyColumn" to column="`MyColumn`" in mapping files).
>
> In fact I like capitalized column/table names (more readable for me),
> but the point is if this affect performance when running queries (for example PgSQL engine
> might take more time to analyze query with double-quoted identifiers or so).
>
> Is there any performance penalty for this ?
As I said, no cost you'll ever notice.
--
Richard Huxton
Archonet Ltd
