Could not create a table named "USER" under postgreSQL

Ying Lu wrote:
[color=blue]
> Hello,
>
> I have a table named "USER" under MySQL database. When I am trying to
> move tables from MySQL to PostgreSQL, I found that I could not create a
> table namely "USER". I guess "USER" is a key string used by PostgreSQL
> system so that we could not create a table named "USER". Is that true?[/color]

You'll have to quote it in all the SQL you use if you insist on
using it:

[test@lexus] create table user (key integer);
ERROR: syntax error at or near "user" at character 14
[test@lexus] create table "user" (key integer);
CREATE TABLE
[test@lexus] insert into user values (1);
ERROR: syntax error at or near "user" at character 13
[test@lexus] insert into "user" values (1);

HTH,

Mike Mascari


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

"Ying Lu" <ying_lu@cs.concordia.ca> did write:

[color=blue]
> Hello,
>
> I have a table named "USER" under MySQL database. When I am trying to
> move tables from MySQL to PostgreSQL, I found that I could not create a
> table namely "USER". I guess "USER" is a key string used by PostgreSQL
> system so that we could not create a table named "USER". Is that true?[/color]

ironically, you actually can create a table named "USER", but
not a table named USER

gnari=# create table "USER" (foo varchar);
CREATE TABLE


gnari




---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Ying Lu <ying_lu@cs.concordia.ca> wrote:
[color=blue]
> Hello,
>
> I have a table named "USER" under MySQL database. When I am trying to
> move tables from MySQL to PostgreSQL, I found that I could not create a
> table namely "USER". I guess "USER" is a key string used by PostgreSQL
> system so that we could not create a table named "USER". Is that true?[/color]

Yes and no.

"user" is a SQL reserved word, which means _no_ SQL database _should_ let
you create a table by that name.

However, if you acutally enclose the name in quotes, you can safely work
around that restriction, i.e.:

CREATE TABLE "USER" AS ...

Be warned that when you enclose the table name in quotes, it becomes
case-sensitive, thus you will have to enclose it in quotes every time
you use it or the names won't match.

A better solution would be to take this opportunity to make your table
names more SQL compliant.

HTH.

--
Bill Moran
Potential Technologies
http://www.potentialtech.com

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to Join Bytes! so that your
message can get through to the mailing list cleanly

Ying Lu <ying_lu@cs.concordia.ca> writes:[color=blue]
> I have a table named "USER" under MySQL database. When I am trying to
> move tables from MySQL to PostgreSQL, I found that I could not create a
> table namely "USER". I guess "USER" is a key string used by PostgreSQL
> system so that we could not create a table named "USER". Is that true?[/color]

USER is a synonym for CURRENT_USER, as required by the SQL standard
(as far back as SQL92). So yes, it's a reserved word. You could
double-quote it if you really want to use it as an identifier.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster