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
-~----------~----~----~----~------~----~------~--~---

Reply via email to