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@xxxxxxxxxxxxxxx +420 608 782 813 -----Original Message----- From: Richard Huxton [mailto:dev@xxxxxxxxxxxx] Sent: Wednesday, April 09, 2008 6:57 PM To: Jozef Ševčík Cc: pgsql-general@xxxxxxxxxxxxxx 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
