Thread: Quotation marks in queries
Hello, I am a total newbie to PostgreSql, coming from MS Access background. I have a question regarding queries in PostgreSql: why do I need to enclose every field name and table name in quotation marks like SELECT "Name" From "contacts" That is a major inconvenience when composing a query string in a VB program. Thank you.
This is only really necessary if the table or column names are mixed cases or include spaces or some such ... normally
(?)this is not required. PostgreSQL relentlessly lower cases such names unless they are double quoted.
HTH,
Greg Williamson
DBA
GlobeXplorer LLC
-----Original Message-----
From: pgsql-general-owner@postgresql.org on behalf of Martynas Brijunas
Sent: Thu 7/14/2005 12:31 AM
To: pgsql-general@postgresql.org
Cc:
Subject: [GENERAL] Quotation marks in queries
Hello,
I am a total newbie to PostgreSql, coming from MS Access background. I have
a question regarding queries in PostgreSql: why do I need to enclose every
field name and table name in quotation marks like
SELECT "Name" From "contacts"
That is a major inconvenience when composing a query string in a VB program.
Thank you.
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
!DSPAM:42d6173839122069320068!
You only need to use quotes in your SQL statements if your table names
in PostgreSQL contain any upper case letters. PostgreSQL automatically
converts all of your SQL statements to lower case unless they're quoted.
As an alternative to quoting in VB (assuming you're using Access as your
FE), store your SQL in tables where you can quote it correctly instead
of having to use " & chr(34) & " or """" every time you want to insert a
quote in VB.
Mike
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Martynas
Brijunas
Sent: Thursday, July 14, 2005 3:32 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Quotation marks in queries
Hello,
I am a total newbie to PostgreSql, coming from MS Access background. I
have
a question regarding queries in PostgreSql: why do I need to enclose
every
field name and table name in quotation marks like
SELECT "Name" From "contacts"
That is a major inconvenience when composing a query string in a VB
program.
Thank you.
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
How difficult would it be to add a configuration option (at the global, database or session level) to make PostgreSQL transparently quote identifiers for you? That would be a simple way to allow users to use case-sensitive names without quoting everything manually or changing deep parts of the back end to support case insensitivity. I'd certainly welcome such a feature... Relyea, Mike wrote: > You only need to use quotes in your SQL statements if your table names > in PostgreSQL contain any upper case letters. PostgreSQL automatically > converts all of your SQL statements to lower case unless they're quoted. > > As an alternative to quoting in VB (assuming you're using Access as your > FE), store your SQL in tables where you can quote it correctly instead > of having to use " & chr(34) & " or """" every time you want to insert a > quote in VB. > > Mike > > > > > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Martynas > Brijunas > Sent: Thursday, July 14, 2005 3:32 AM > To: pgsql-general@postgresql.org > Subject: [GENERAL] Quotation marks in queries > > Hello, > > I am a total newbie to PostgreSql, coming from MS Access background. I > have > a question regarding queries in PostgreSql: why do I need to enclose > every > field name and table name in quotation marks like > > SELECT "Name" From "contacts" > > That is a major inconvenience when composing a query string in a VB > program. > Thank you. > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster >
The easiest solution is just not to use caps or spaces in your table/object names, there is no advantage to doing so. People just need to get over the fact that having caps in a name make it easier to read. My Test Table should be my_test_table, the naming makes no difference to the application using the table. Same thing with ordering of fields in a table, it makes no difference other than for looks if the fields are in the order you want them to be in. It is much more of a pain to qoute your sql than it is to have it look nice. Just my 2 cents on the subject. Tony Relyea, Mike wrote: >You only need to use quotes in your SQL statements if your table names >in PostgreSQL contain any upper case letters. PostgreSQL automatically >converts all of your SQL statements to lower case unless they're quoted. > >As an alternative to quoting in VB (assuming you're using Access as your >FE), store your SQL in tables where you can quote it correctly instead >of having to use " & chr(34) & " or """" every time you want to insert a >quote in VB. > >Mike > > > >
Tony Caduto wrote: > The easiest solution is just not to use caps or spaces in your > table/object names, there is no advantage to doing so. > People just need to get over the fact that having caps in a name make it > easier to read. Not to pick nits, but I disagree. Capitalization (CamelCase in particular) can make it easier to determine at a glance what type of object a name refers to. While this can be determined from syntax, it's nice not to have to think about it. ;) > It is much more of a pain to qoute your sql than it is to have it look > nice. Certainly. Things don't look very nice with quotes all over the place. -- Peter Fein pfein@pobox.com 773-575-0694 Basically, if you're not a utopianist, you're a schmuck. -J. Feldman
Tony Caduto wrote: > The easiest solution is just not to use caps or spaces in your > table/object names, there is no advantage to doing so. > People just need to get over the fact that having caps in a name make it > easier to read. > > My Test Table should be my_test_table, the naming makes no difference > to the application using the table. > I agree about the spacing part (though others wouldn't and who am I to tell them what to think?), but from a personal point of view I vastly prefer camel caps to underscores in all situations. It's a pain for me to have to use lower case + underscores in the database when the rest of my code uses camel caps. It's also a pain to have to quote all identifiers to be able to use camel caps. Providing a config option to silently quote all identifiers submitted would solve the problem without having any effect on those who like the underscore style. > Same thing with ordering of fields in a table, it makes no difference > other than for looks if the fields are in the order you want them > to be in. > Agreed, but having the fields ordered logically can be a form of self-documentation. i.e. having the PK always at the start, grouping closely-related fields together etc. The application using the table should definitely not depends on field ordering though. > It is much more of a pain to qoute your sql than it is to have it look > nice. > Absolutely! That's why I was wondering about a configuration option to make pg automatically quote all identifiers. > Just my 2 cents on the subject. > > Tony > > Relyea, Mike wrote: > >> You only need to use quotes in your SQL statements if your table names >> in PostgreSQL contain any upper case letters. PostgreSQL automatically >> converts all of your SQL statements to lower case unless they're quoted. >> >> As an alternative to quoting in VB (assuming you're using Access as your >> FE), store your SQL in tables where you can quote it correctly instead >> of having to use " & chr(34) & " or """" every time you want to insert a >> quote in VB. >> >> Mike >> >> >> >> > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster >
Tony Caduto wrote: > The easiest solution is just not to use caps or spaces in your > table/object names, there is no advantage to doing so. > People just need to get over the fact that having caps in a name make it > easier to read. > > My Test Table should be my_test_table, the naming makes no difference > to the application using the table. > > Same thing with ordering of fields in a table, it makes no difference > other than for looks if the fields are in the order you want them > to be in. > > It is much more of a pain to qoute your sql than it is to have it look > nice. > > Just my 2 cents on the subject. > The problem we have is that we want to migrate to postgresql from our current sql server db, but the problem with caps requiring quotes around them makes this a far from easy migration.
# teknokrat@yahoo.com / 2005-07-20 12:43:48 +0100:
> Tony Caduto wrote:
> >The easiest solution is just not to use caps or spaces in your
> >table/object names, there is no advantage to doing so.
> >People just need to get over the fact that having caps in a name make it
> >easier to read.
> >
> >My Test Table should be my_test_table, the naming makes no difference
> >to the application using the table.
> >
> >Same thing with ordering of fields in a table, it makes no difference
> >other than for looks if the fields are in the order you want them
> >to be in.
> >
> >It is much more of a pain to qoute your sql than it is to have it look
> >nice.
> >
> >Just my 2 cents on the subject.
> >
>
> The problem we have is that we want to migrate to postgresql from our
> current sql server db, but the problem with caps requiring quotes around
> them makes this a far from easy migration.
Just so that it doesn't look like your problems are caused by
PostgreSQL: it is in accordance with SQL:1999, with the exception
that SQL says "fold to uppercase", but PostgreSQL folds to lowercase.
IOW, if you require that "table" <> "Table" <> "TABLE", then SQL is
the wrong langaue.
--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man. You don't KNOW.
Cause you weren't THERE. http://bash.org/?255991
On Wed, Jul 20, 2005 at 12:43:48PM +0100, teknokrat wrote: > The problem we have is that we want to migrate to postgresql from our > current sql server db, but the problem with caps requiring quotes around > them makes this a far from easy migration. The rule is pretty much, either always quote or never quote. Once you start mixing and matching you're likely to get screwed. So, if you don't use quotes in the CREATE TABLE statement, you'll never have to quote anywhere else either... Have this helps, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment
well, you could always write a little function that would loop through every object and then rename to lower case and replace all spaces with _ underscores. Then in your application code just rename everything accordingly. Once again, even if you are using M$ SQL server it would be a good idea to avoid filenames with caps and spaces, I guess just keep that in mind for future projects. I have converted large access databases where the users just used whatever the heck they wanted just by renaming everything and it did not take that long. It would be worth it in the long run to do. Good luck with your conversion. Tony Caduto http://www.amsoftwaredesign.com Home of PG Lighting Admin for Postgresql 8.x teknokrat wrote: > The problem we have is that we want to migrate to postgresql from our > current sql server db, but the problem with caps requiring quotes > around them makes this a far from easy migration. > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >
That's a really good point about the create table, you can actually just rename everything in you create table statements before you actually do any data import, then as long as the fields are in the same physical order(does not matter if the names are different) you can output data from the source system as tab delimited and then use the postgresql copy command to import the tab delimited file to postgresql. Tony Caduto http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql 8.x > >So, if you don't use quotes in the CREATE TABLE statement, you'll never >have to quote anywhere else either... > >Have this helps, > >