Re: Could not create a table named "USER" under postgreSQL
Re: Could not create a table named "USER" under postgreSQL
От:
"gnari" <gnari@simnet.is>
Дата:
"Ying Lu" 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
От:
Bill Moran <wmoran@potentialtech.com>
Дата:
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? 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
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Ying Lu 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
Could not create a table named "USER" under postgreSQL
От:
Ying Lu <ying_lu@cs.concordia.ca>
Дата:
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
От:
Mike Mascari <mascarm@mascari.com>
Дата:
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
