Quotation marks in queries

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
12 messages Options
Reply | Threaded
Open this post in threaded view
|

Quotation marks in queries

Martynas Brijunas-2
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 [hidden email] so that your
       message can get through to the mailing list cleanly
Reply | Threaded
Open this post in threaded view
|

Re: Quotation marks in queries

Gregory S. Williamson

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: [hidden email] on behalf of Martynas Brijunas
Sent: Thu 7/14/2005 12:31 AM
To: [hidden email]
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 [hidden email] so that your
       message can get through to the mailing list cleanly

!DSPAM:42d6173839122069320068!





---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org
Reply | Threaded
Open this post in threaded view
|

Re: Quotation marks in queries

Relyea, Mike
In reply to this post by Martynas Brijunas-2
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: [hidden email]
[mailto:[hidden email]] On Behalf Of Martynas
Brijunas
Sent: Thursday, July 14, 2005 3:32 AM
To: [hidden email]
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 [hidden email] so that your
       message can get through to the mailing list cleanly

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
Reply | Threaded
Open this post in threaded view
|

Re: Quotation marks in queries

pickscrape
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: [hidden email]
> [mailto:[hidden email]] On Behalf Of Martynas
> Brijunas
> Sent: Thursday, July 14, 2005 3:32 AM
> To: [hidden email]
> 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 [hidden email] so that your
>        message can get through to the mailing list cleanly
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
Reply | Threaded
Open this post in threaded view
|

Re: Quotation marks in queries

snorkel
In reply to this post by Relyea, Mike
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
>
>
>  
>


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
Reply | Threaded
Open this post in threaded view
|

Re: Quotation marks in queries

Pete-110
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                 [hidden email]                 773-575-0694

Basically, if you're not a utopianist, you're a schmuck. -J. Feldman

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org
Reply | Threaded
Open this post in threaded view
|

Re: Quotation marks in queries

pickscrape
In reply to this post by snorkel
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
>


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org
Reply | Threaded
Open this post in threaded view
|

Re: Quotation marks in queries

teknokrat
In reply to this post by snorkel
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.

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [hidden email] so that your
       message can get through to the mailing list cleanly
Reply | Threaded
Open this post in threaded view
|

Re: Quotation marks in queries

Roman Neuhauser
# [hidden email] / 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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
Reply | Threaded
Open this post in threaded view
|

Re: Quotation marks in queries

Martijn van Oosterhout
In reply to this post by teknokrat
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   <[hidden email]>   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.

attachment0 (240 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Quotation marks in queries

snorkel
In reply to this post by teknokrat
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 [hidden email] so that your
>       message can get through to the mailing list cleanly
>


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [hidden email] so that your
       message can get through to the mailing list cleanly
Reply | Threaded
Open this post in threaded view
|

Re: Quotation marks in queries

snorkel
In reply to this post by Martijn van Oosterhout
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,
>  
>


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [hidden email] so that your
       message can get through to the mailing list cleanly