Re: [GENERAL] rationale behind quotes for camel case?
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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
Re: [GENERAL] rationale behind quotes for camel case?
On 06/28/11 2:12 AM, fluca1...@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 pierceN 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] rationale behind quotes for camel case?
On Tue, Jun 28, 2011 at 4:12 AM, fluca1...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] rationale behind quotes for camel case?
On Tue, Jun 28, 2011 at 8:43 AM, Merlin Moncure mmonc...@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.
Re: [GENERAL] rationale behind quotes for camel case?
fluca1...@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 USg...@2ndquadrant.com Baltimore, MD Comprehensive and Customized PostgreSQL Training Classes: http://www.2ndquadrant.us/postgresql-training/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] rationale behind quotes for camel case?
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] rationale behind quotes for camel case?
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_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] rationale behind quotes for camel case?
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 a...@anvilwalrusden.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] rationale behind quotes for camel case?
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 USg...@2ndquadrant.com Baltimore, MD Comprehensive and Customized PostgreSQL Training Classes: http://www.2ndquadrant.us/postgresql-training/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general