Postgres Pro
Downloads
Home   >   mailing lists

Thread: Could not create a table named "USER" under postgreSQL

Could not create a table named "USER" under postgreSQL

From
Ying Lu
Date:
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

Re: Could not create a table named "USER" under postgreSQL

From
Mike Mascari
Date:
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

Re: Could not create a table named "USER" under postgreSQL

From
"gnari"
Date:
"Ying Lu" <ying_lu@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

Re: Could not create a table named "USER" under

From
Bill Moran
Date:
Ying Lu <ying_lu@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

Re: Could not create a table named "USER" under postgreSQL

From
Tom Lane
Date:
Ying Lu <ying_lu@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