Re: Quotation marks in queries

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(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Martynas
Brijunas
Sent: Thursday, July 14, 2005 3:32 AM
To: pgsql-general(at)postgresql(dot)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(at)postgresql(dot)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(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Martynas
> Brijunas
> Sent: Thursday, July 14, 2005 3:32 AM
> To: pgsql-general(at)postgresql(dot)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(at)postgresql(dot)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(at)pobox(dot)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(at)yahoo(dot)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(at)svana(dot)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.

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(at)postgresql(dot)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,
>
>