Could not create a table named "USER" under postgreSQL

[Ying Lu]

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?

Thanks a lot,
Emi Lu
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Mike Mascari]

Ying Lu wrote:

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?

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

[gnari]

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

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?

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

[Bill Moran]

Ying Lu <yi*****@cs.concordia.ca> wrote:

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?

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 ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Tom Lane]

Ying Lu <yi*****@cs.concordia.ca> writes:

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?

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