Enclosing the name in double quotes is Postgres's way of allowing you to override the name rules. In other words, it basically allows you to name the table anything you want. However, it's use it not standard across all database engines. Different database engines have different ways to override the naming rules. For instance, with MSSQL you can enclose the table name with brackets, e.g. create table [user].
What I find interesting about this is that you can even use table names with embedded spaces. For example: -- MSSQL: create table [user name] (id int) drop table [user name] -- Postgres create table "user name" (id int); drop table "user name" Should the DAL know how to enclose table names for each supported database? I don't think so but I definitely can see some advantages. Me personally? I rather avoid using reserved words as table name. Avoiding reserved words leads to less confusion in the long run, in my opinion. Take for example naming a table in Postgres "user". When you look at the table inside a tool like pgAdmin the table name is not quoted. However, a table created with a quoted name must always be referenced with the quoted name. In other words, select * from user will return a different result set then select * from "user" Here's an article covering bit more on using quoted table names in Postgres. http://www.informit.com/articles/article.aspx?p=30669 Another thing to keep in mind with Postgres is that quoted table names are case sensitive. As stated in the bug report below: - identifiers specified without double-quotes are folded to lower case - identifiers specified with double-quotes are not case folded http://archives.postgresql.org/pgsql-bugs/2005-05/msg00173.php So while the following will work: create table ATable (id integer); select * from atable; select * from ATABLE; The following select statements will not work: create table "ATable" (id integer); select * from atable; select * from ATABLE; And here is something else that can lead to confusion: create table "ATable" (id integer); create table ATable (id integer); Now you have 2 tables existing in the same database that appear to have the same name. Grant one table is called "ATable" and the other is atable. Still it can be confusing. -KIRBY On Jan 5, 2009, at 6:58 PM, Jeff Koftinoff wrote: > > Well, this is interesting. With the proper quoting of the SQL command > you can create a table named "user" with no problems on postgresql: > > if2k9=# create table user ( did integer, name varchar(40) ); > ERROR: syntax error at or near "user" > LINE 1: create table user ( did integer, name varchar(40) ); > > mydb=# create table "user" ( did integer, name varchar(40) ); > CREATE TABLE > mydb=# insert into "user" VALUES (1,'Jeff Koftinoff'); > INSERT 0 1 > mydb=# select * from "user"; > did | name > -----+---------------- > 1 | Jeff Koftinoff > (1 row) > > So the appropriate fix is to fix the way web2py quotes the sql > queries. > > Regards, > Jeff Koftinoff > www.jdkoftinoff.com > > On Dec 29 2008, 1:37 pm, "Yarko Tymciurak" <yark...@gmail.com> wrote: >> A couple of things: >> >> "name" is not a "reserved keyword" (see URL Fran referenced) - >> which means >> "name" is OK as a table or column name. And since "name" doesn't >> appear >> reserved in any of the SQL standards listed, I think this should be >> ok in >> other backends also... >> >> 'user' however _IS_ a reserved keyword in all the SQL standards >> listed in >> that link. You can modify it to be >> 'user_' which will always be safe, or some other thing - >> 'my_user', etc. >> >> As Fran pointed out, anything in that list which is reserved in any >> version >> of SQL is just not a fair table or column name for any database >> (not just >> Postgres) >> >> Regards, >> Yarko >> >> On Mon, Dec 29, 2008 at 3:10 PM, Fran <francisb...@googlemail.com> >> wrote: >> >>> On Dec 29, 9:03 pm, Alex <shes...@gmail.com> wrote: >>>> So only solution to rename tables and fields everywhere ? >> >>> AFAIK, yes...sorry... >> >>> F > > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "web2py Web Framework" group. To post to this group, send email to web2py@googlegroups.com To unsubscribe from this group, send email to web2py+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/web2py?hl=en -~----------~----~----~----~------~----~------~--~---
