creating "user" table

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/&gt;
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/&gt;

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

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