By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,569 Members | 1,386 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,569 IT Pros & Developers. It's quick & easy.

Could not create a table named "USER" under postgreSQL

P: n/a
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

Nov 23 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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

Nov 23 '05 #2

P: n/a
"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

Nov 23 '05 #3

P: n/a
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

Nov 23 '05 #4

P: n/a
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

Nov 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.