Thread: creating "user" table
Hi, This one is more for the developers, but other comments welcome. Since we can't create a table called user in a normal database due to the reserved keyword problem, would it be easy or worthwhile just having the CREATE USER information in the template1 database? Just a suggestion to fix the problem, it's just a really annoying problem :/ Chris.
On Sat, 22 Sep 2001, Chris wrote: > Hi, > > This one is more for the developers, but other comments welcome. > > Since we can't create a table called user in a normal database due to > the reserved keyword problem, would it be easy or worthwhile just having > the CREATE USER information in the template1 database? Uhhh, what "CREATE USER information"? Vince. -- ========================================================================== Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net 56K Nationwide Dialup from $16.00/mo at Pop4 Networking Online Campground Directory http://www.camping-usa.com Online Giftshop Superstore http://www.cloudninegifts.com ==========================================================================
> > Hi, > > > > This one is more for the developers, but other comments welcome. > > > > Since we can't create a table called user in a normal database due to > > the reserved keyword problem, would it be easy or worthwhile just having > > the CREATE USER information in the template1 database? > >Uhhh, what "CREATE USER information"? Sorry, thought I was clear. I meant only be able to create users when you're connected to the template1 db. so test=# create user x; wouldn't work, since you're not using template1. that way the user keyword would be free to use as a table name. Chris.
Chris writes: > I meant only be able to create users when you're connected to the template1 db. > > so > test=# create user x; > > wouldn't work, since you're not using template1. > > that way the user keyword would be free to use as a table name. But the parser is fixed at compile time and doesn't change depending on the database you're connected to. -- Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
> > I meant only be able to create users when you're connected to the > template1 db. > > > > so > > test=# create user x; > > > > wouldn't work, since you're not using template1. > > > > that way the user keyword would be free to use as a table name. > >But the parser is fixed at compile time and doesn't change depending on >the database you're connected to. Fair enough. There goes that idea then. Could the parser do a different regex for the word user depending on syntax? Just throwing around some (probably stupid) ideas, I have no idea on how difficult / worthwhile this would actually be. Chris.
Chris wrote: > > Could the parser do a different regex for the word user depending on > syntax? > > Just throwing around some (probably stupid) ideas, I have no idea on how > difficult / worthwhile this would actually be. Difficult? No idea. Worthwile? Not at all I think, since both SQL92 and SQL99 say that 'user' is a reserved word, so even if PostgreSQL coul allow the use of user it shouldn't ;) Jochem
>>>>> "Chris" == Chris <tomcat@weirdness.com> writes:
Chris> Hi,
Chris> This one is more for the developers, but other comments welcome.
Chris> Since we can't create a table called user in a normal database due to
Chris> the reserved keyword problem,
False precondition!
template1=# create database demo;
CREATE DATABASE
template1=# \c demo
You are now connected to database demo.
demo=# create table "user" (name text, age int);
CREATE
demo=# \d
List of relations
Name | Type | Owner
------+-------+--------
user | table | merlyn
(1 row)
demo=# \d user
Table "user"
Attribute | Type | Modifier
-----------+---------+----------
name | text |
age | integer |
demo=# insert into "user" values ('Randal', 39);
INSERT 1034607 1
demo=# insert into "user" values ('Russ', 38);
INSERT 1034608 1
demo=# insert into "user" values ('Ron', 35);
INSERT 1034609 1
demo=# select * from "user" order by age;
name | age
--------+-----
Ron | 35
Russ | 38
Randal | 39
(3 rows)
demo=#
You can quote any reserved word to get any name you want.
You just need to think of the table name of demo as _ "demo" _.
--
Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095
<merlyn@stonehenge.com> <URL:http://www.stonehenge.com/merlyn/>
Perl/Unix/security consulting, Technical writing, Comedy, etc. etc.
See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!
Thanks for the information !!
delyx.
Daniel Åkerud.
> >>>>> "Chris" == Chris <tomcat@weirdness.com> writes:
>
> Chris> Hi,
> Chris> This one is more for the developers, but other
comments welcome.
>
> Chris> Since we can't create a table called user in a
normal database due to
> Chris> the reserved keyword problem,
>
> False precondition!
>
> template1=# create database demo;
> CREATE DATABASE
> template1=# \c demo
> You are now connected to database demo.
> demo=# create table "user" (name text, age int);
> CREATE
> demo=# \d
> List of relations
> Name | Type | Owner
> ------+-------+--------
> user | table | merlyn
> (1 row)
>
> demo=# \d user
> Table "user"
> Attribute | Type | Modifier
> -----------+---------+----------
> name | text |
> age | integer |
>
> demo=# insert into "user" values ('Randal', 39);
> INSERT 1034607 1
> demo=# insert into "user" values ('Russ', 38);
> INSERT 1034608 1
> demo=# insert into "user" values ('Ron', 35);
> INSERT 1034609 1
> demo=# select * from "user" order by age;
> name | age
> --------+-----
> Ron | 35
> Russ | 38
> Randal | 39
> (3 rows)
>
> demo=#
>
> You can quote any reserved word to get any name you want.
> You just need to think of the table name of demo as
_ "demo" _.
>
> --
> Randal L. Schwartz - Stonehenge Consulting Services,
Inc. - +1 503 777 0095
> <merlyn@stonehenge.com>
<URL:http://www.stonehenge.com/merlyn/>
> Perl/Unix/security consulting, Technical writing, Comedy,
etc. etc.
> See PerlTraining.Stonehenge.com for onsite and open-
enrollment Perl training!
>
> ---------------------------(end of broadcast)-------------
--------------
> TIP 3: if posting/reading through Usenet, please send an
appropriate
> subscribe-nomail command to majordomo@postgresql.org so
that your
> message can get through to the mailing list cleanly
>
Hey,
> > Chris> Hi,
> > Chris> This one is more for the developers, but other
>comments welcome.
> >
> > Chris> Since we can't create a table called user in a
>normal database due to
> > Chris> the reserved keyword problem,
> >
> > False precondition!
> >
> > template1=# create database demo;
> > CREATE DATABASE
> > template1=# \c demo
> > You are now connected to database demo.
> > demo=# create table "user" (name text, age int);
> > CREATE
> > demo=# \d
> > List of relations
> > Name | Type | Owner
> > ------+-------+--------
> > user | table | merlyn
> > (1 row)
> >
> > demo=# \d user
> > Table "user"
> > Attribute | Type | Modifier
> > -----------+---------+----------
> > name | text |
> > age | integer |
> >
> > demo=# insert into "user" values ('Randal', 39);
> > INSERT 1034607 1
> > demo=# insert into "user" values ('Russ', 38);
> > INSERT 1034608 1
> > demo=# insert into "user" values ('Ron', 35);
> > INSERT 1034609 1
> > demo=# select * from "user" order by age;
> > name | age
> > --------+-----
> > Ron | 35
> > Russ | 38
> > Randal | 39
> > (3 rows)
> >
> > demo=#
> >
> > You can quote any reserved word to get any name you want.
> > You just need to think of the table name of demo as
>_ "demo" _.
> >
True, didn't give that example, but.. try that in another DBMS :) I know
(at least) MySQL won't let you do that (*waits for the obvious comment*),
don't know about other DBMS needing quotes around the name, but I don't
think that one is portable to other systems :/
As Jochem pointed out in another post, by definition it's a reserved word
(both sql92 & 99) so I'll go back to my corner again :)
-----------------
Chris Smith
http://www.squiz.net/
Chris wrote:
>
> Hey,
...
>
> > > False precondition!
> > >
> > > template1=# create database demo;
> > > CREATE DATABASE
> > > template1=# \c demo
> > > You are now connected to database demo.
> > > demo=# create table "user" (name text, age int);
...
>
> True, didn't give that example, but.. try that in another DBMS :) I know
> (at least) MySQL won't let you do that (*waits for the obvious comment*),
> don't know about other DBMS needing quotes around the name, but I don't
> think that one is portable to other systems :/
Its at least portable to Oracle, which behaves the same way as
PostgreSQL, BTW:
Connected to:
Oracle8 Enterprise Edition Release 8.0.5.0.0 - Production
PL/SQL Release 8.0.5.0.0 - Production
SQL> CREATE TABLE USER (key INTEGER);
CREATE TABLE USER (key INTEGER)
*
ERROR at line 1:
ORA-00903: invalid table name
SQL> CREATE TABLE "user" (key INTEGER);
Table created.
>
> As Jochem pointed out in another post, by definition it's a reserved word
> (both sql92 & 99) so I'll go back to my corner again :)
Mike Mascari
mascarm@mascari.com