Here is a link to the Postgres documentation that talks about identifiers and key words.
http://www.postgresql.org/docs/8.3/interactive/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS I didn't have the URL handy when I sent the previous reply. -KIRBY On Jan 5, 2009, at 8:25 PM, Kirby Turner wrote: > > 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 -~----------~----~----~----~------~----~------~--~---
