Thread: rationale behind quotes for camel case?
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?
Thanks,
Luca
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?
Thanks,
Luca
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 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 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 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