rationale behind quotes for camel case?

On 06/28/11 2:12 AM, fluca1978@infinito.it wrote:

Hi all,
first of all I'm not expressing any critique against the use of quotes
for identifier expressed using camel case. However a lot of new
postgresql users seems to be unhappy with the use of quotes for camel
case identifiers, so I'd like to know what is the rationale behind it.
I mean, is a feature of the partser or is a need for it to work?
Anyone can point me to an explaination?

the SQL spec states that all identifiers are case independent unless
quoted. In fact, the spec says to upshift everything to all capitals
(probably reflecting its 1980s IBM origins), but PG decided a long time
ago to default to lower case, since the final result has the same effect.

--
john r pierce N 37, W 122
santa cruz ca mid-left coast

On Tue, Jun 28, 2011 at 4:12 AM, <fluca1978@infinito.it> wrote:

Hi all,
first of all I'm not expressing any critique against the use of quotes for
identifier expressed using camel case. However a lot of new postgresql users
seems to be unhappy with the use of quotes for camel case identifiers, so
I'd like to know what is the rationale behind it.

A few reasons:
*) aesthetics: given two queries that express the same thing, I'd
prefer the one with no " vs lots of ". Quoted identifiers do not
increase code clarity nor provide any technical benefit beyond being
able to mix identifiers by case, which most would agree is a terrible
idea.
*) once you decide to quote your identifiers like that, you have to
always do it. not just formal code, but queries in psql, etc. having
to type it out all the time is time consuming and distracting. (by the
way, I switched to lower case keywords a long time ago on similar
grounds)
*) lousy tools: sooner or later you may bump into a tool that
generates sql improperly without the quoted identifiers.

The rebuttal to the above points is that the problem with not quoting
is that your identifiers are folded to lower case on the server which
can make them difficult to read in psql, pgadmin, etc. when outputted.
This is true and I consequently use underscores to break up words in
my personal style (order_number, not OrderNumber), avoiding that
problem. Some people don't like it, but it is the easiest to type,
the most tool friendly, and most regular.

merlin

On Tue, Jun 28, 2011 at 04:00:42AM -0700, John R Pierce wrote:

capitals (probably reflecting its 1980s IBM origins), but PG decided
a long time ago to default to lower case, since the final result has
the same effect.

Well, it _sort of_ has the same effect. In a traditional SQL
environment, if you use TABLENAME and "TABLENAME", they're equivalent,
but tablename and "TABLENAME" are not. In PostgreSQL, that doesn't
work: tablename and "tablename" are equivalent instead. This
sometimes bites people when moving to Postgres, because their
identifier quoting hasn't been consistent in their application.

A

--
Andrew Sullivan
ajs@anvilwalrusden.com

On Tue, Jun 28, 2011 at 8:43 AM, Merlin Moncure <mmoncure@gmail.com> wrote:

The rebuttal to the above points is that the problem with not quoting
is that your identifiers are folded to lower case on the server which
can make them difficult to read in psql, pgadmin, etc. when outputted.
This is true and I consequently use underscores to break up words in
my personal style (order_number, not OrderNumber), avoiding that
problem. Some people don't like it, but it is the easiest to type,
the most tool friendly, and most regular.

merlin

(wandering slightly off-topic)

Any suggestions on how to name tables when table names contain both
multi-word nouns and mutli-table "many-many" mappings?

Example: Suppose that I have a table called "foo" and another table called
"barBiz" (or "bar_biz" if you prefer). Further, both of these tables have a
"serial" primary key. Now I want to create a third table that represents a
many-to-many relationship between "foo" and "barBiz". So far I have been
keeping compound-noun table names in camel case, but mapping tables separate
the base table names with underscores. Thus the table name would be
"foo_barBiz".

However, I find the above distasteful, for many of the reasons that Merlin
and others have outlined. Yet naming the table "foo_bar_biz" seems
ambiguous to me, as does using just lower-case "foo_barbiz" / "barbiz".
These examples are contrived. The real table names are normal English words
with subjective meaning.

I'd like in ask the pgsql community for suggestions on how they name tables.

Thank you all for your time.

fluca1978@infinito.it wrote:

first of all I'm not expressing any critique against the use of quotes
for identifier expressed using camel case. However a lot of new
postgresql users seems to be unhappy with the use of quotes for camel
case identifiers, so I'd like to know what is the rationale behind it.
I mean, is a feature of the partser or is a need for it to work?
Anyone can point me to an explaination?

The need for quoting and use of CamelCase are only vaguely related to
one another. There are situations other than CamelCase where quotes are
needed, and using CamelCase doesn't necessarily require quotes.

If you follow the end of the documentation at
http://www.postgresql.org/docs/9.0/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS
you'll find this explanation of what the quoting issue is all about:

Quoting an identifier also makes it case-sensitive, whereas unquoted
names are always folded to lower case. For example, the identifiers FOO,
foo, and "foo" are considered the same by PostgreSQL, but "Foo" and
"FOO" are different from these three and each other. (The folding of
unquoted names to lower case in PostgreSQL is incompatible with the SQL
standard, which says that unquoted names should be folded to upper case.
Thus, foo should be equivalent to "FOO" not "foo" according to the
standard. If you want to write portable applications you are advised to
always quote a particular name or never quote it.)

So saying you're unhappy with the need for quoting of mixed-case items
isn't going to get you very far, as the behavior here is all tied up
with the trivia of the SQL spec. The specification intends that
anything other than all upper-case naming requires quoting, and
PostgreSQL turns that around to say that everything goes to lower-case
by default. It's actually possible to leave out the quoting if you
really want to, but as advised here you have to be consistent about it.
Once you've created something with a fully quoted name, instead of
letting it "fold" the name to all lower-case, you must continue to
reference it that way in the future. There's nothing stopping you from
just never quoting anything though.

--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
Comprehensive and Customized PostgreSQL Training Classes:
http://www.2ndquadrant.us/postgresql-training/

On Tue, Jun 28, 2011 at 11:09:52AM -0500, dennis jenkins wrote:

Any suggestions on how to name tables when table names contain both
multi-word nouns and mutli-table "many-many" mappings?

Example: Suppose that I have a table called "foo" and another table called
"barBiz" (or "bar_biz" if you prefer). Further, both of these tables have a
"serial" primary key. Now I want to create a third table that represents a
many-to-many relationship between "foo" and "barBiz". So far I have been
keeping compound-noun table names in camel case, but mapping tables separate
the base table names with underscores. Thus the table name would be
"foo_barBiz".

However, I find the above distasteful, for many of the reasons that Merlin
and others have outlined. Yet naming the table "foo_bar_biz" seems
ambiguous to me, as does using just lower-case "foo_barbiz" / "barbiz".
These examples are contrived. The real table names are normal English words
with subjective meaning.

I'd like in ask the pgsql community for suggestions on how they name tables.

GNUmed uses

lnk_foo2bar_biz

or

lnk_bar_biz2foo

depending on what makes more sense.

Karsten
--
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

On Tue, Jun 28, 2011 at 11:09:52AM -0500, dennis jenkins wrote:

Any suggestions on how to name tables when table names contain both
multi-word nouns and mutli-table "many-many" mappings?

Example: Suppose that I have a table called "foo" and another table called
"barBiz" (or "bar_biz" if you prefer). Further, both of these tables have a
"serial" primary key. Now I want to create a third table that represents a
many-to-many relationship between "foo" and "barBiz". So far I have been
keeping compound-noun table names in camel case, but mapping tables separate
the base table names with underscores. Thus the table name would be
"foo_barBiz".

However, I find the above distasteful, for many of the reasons that Merlin
and others have outlined. Yet naming the table "foo_bar_biz" seems
ambiguous to me, as does using just lower-case "foo_barbiz" / "barbiz".
These examples are contrived. The real table names are normal English words
with subjective meaning.

I'd like in ask the pgsql community for suggestions on how they name tables.

Well, when I avoid camel case, then I use _ to separate words in a table name, and __ to separate table names.

Likewise with column names for foreign keys, __ between table and column name.

--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice

On 06/28/2011 12:09 PM, dennis jenkins wrote:

Example: Suppose that I have a table called "foo" and another table
called "barBiz" (or "bar_biz" if you prefer). Further, both of these
tables have a "serial" primary key. Now I want to create a third
table that represents a many-to-many relationship between "foo" and
"barBiz". So far I have been keeping compound-noun table names in
camel case, but mapping tables separate the base table names with
underscores. Thus the table name would be "foo_barBiz".

The options are basically:

1) Use case to help sort this out

2) Use lots of underscores and cope with the ambiguity

3) Pick something to put in the middle to represent relationships
between things, to make them less ambiguous. You might name this
foo_to_barbiz or the compact but expressive foo2barbiz as two examples.

--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
Comprehensive and Customized PostgreSQL Training Classes:
http://www.2ndquadrant.us/postgresql-training/

Le mardi 28 juin 2011 ￜ 11:09 -0500, dennis jenkins a ￜcrit :

Any suggestions on how to name tables when table names contain both
multi-word nouns and mutli-table "many-many" mappings?

[..]

The real table names are normal English words with subjective
meaning.

Not sure what you mean by suggestive meaning? are the tables called
things like 'cool' and 'uncool', for instance?

I like to keep things clear, so for instance to record customers and
what services they subscribe to, I'd write :

create table customer (id_customer serial, libelle text);
create table service (id_service serial, libelle text);
create table customer_service (id_customer integer, id_service integer);

+ foreign key constraints, obviously.

It's not always simple to find appropriate names. But I take the time,
because it makes it *much* easier for me when I write/debug the SQL in
my Perl modules.

--
Vincent Veyron
http://marica.fr/
Logiciel de gestion des sinistres et des contentieux pour le service juridique