[GENERAL] Could not create a table named "USER" under postgreSQL
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
[GENERAL] Could not create a table named "USER" under postgreSQL
Hello, I have a question about "date" & "timestamp" types in PostgreSQL. I want to setup the default value '-00-00' and "-00-00 00:00:00" for them. However, it seems that PostgreSQL does not support it. Could someone helps me please? The example table: T1 (col1 varchar(7) not null, col2 varchar(4) not null, col3 date not null, col 4 varchar(3), primary key(col1, col2, col3) ) In my design model, "col3" has to be one of the primary key part. Since at the beginning of the data population, we do not know the value of "col3"; values for "col3" are input throught GUI. Therefore, when I use MySQL, the default values I gave is "-00-00". However, after I migrate to postgreSQL, I could not setup the default values as "-00-00" any more. Could somebody help me about it please? I'd like to know how I can save '-00-00' as the default value for "date" and "timestamp" types. By the way, I also tried "my2pg.pl" to migrate table structures got by mysqldump to postgreSQL. The places I have '000-00-00' have been changed to '0001-01-01' by this perl script. Thanks a lot in advance, Emi Lu ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Could not create a table named "USER" under postgreSQL
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: [EMAIL PROTECTED] create table user (key integer); ERROR: syntax error at or near "user" at character 14 [EMAIL PROTECTED] create table "user" (key integer); CREATE TABLE [EMAIL PROTECTED] insert into user values (1); ERROR: syntax error at or near "user" at character 13 [EMAIL PROTECTED] insert into "user" values (1); HTH, Mike Mascari ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Could not create a table named "USER" under postgreSQL
"Ying Lu" <[EMAIL PROTECTED]> 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
Re: [GENERAL] Could not create a table named "USER" under postgreSQL
According to the current SQL language ANSI/ISO standard, the following are reserved words: ::= ABS | ALL | ALLOCATE | ALTER | AND | ANY | ARE | ARRAY | AS | ASENSITIVE | ASYMMETRIC | AT | ATOMIC | AUTHORIZATION | AVG | BEGIN | BETWEEN | BIGINT | BINARY | BLOB | BOOLEAN | BOTH | BY | CALL | CALLED | CARDINALITY | CASCADED | CASE | CAST | CEIL | CEILING | CHAR | CHAR_LENGTH | CHARACTER | CHARACTER_LENGTH | CHECK | CLOB | CLOSE | COALESCE | COLLATE | COLLECT | COLUMN | COMMIT | CONDITION | CONNECT | CONSTRAINT | CONVERT | CORR | CORRESPONDING | COUNT | COVAR_POP | COVAR_SAMP | CREATE | CROSS | CUBE | CUME_DIST | CURRENT | CURRENT_DATE | CURRENT_DEFAULT_TRANSFORM_GROUP | CURRENT_PATH | CURRENT_ROLE | CURRENT_TIME | CURRENT_TIMESTAMP | CURRENT_TRANSFORM_GROUP_FOR_TYPE | CURRENT_USER | CURSOR | CYCLE | DATE | DAY | DEALLOCATE | DEC | DECIMAL | DECLARE | DEFAULT | DELETE | DENSE_RANK | DEREF | DESCRIBE | DETERMINISTIC | DISCONNECT | DISTINCT | DOUBLE | DROP | DYNAMIC | EACH | ELEMENT | ELSE | END | END-EXEC | ESCAPE | EVERY | EXCEPT | EXEC | EXECUTE | EXISTS | EXP | EXTERNAL | EXTRACT | FALSE | FETCH | FILTER | FLOAT | FLOOR | FOR | FOREIGN | FREE | FROM | FULL | FUNCTION | FUSION | GET | GLOBAL | GRANT | GROUP | GROUPING | HAVING | HOLD | HOUR | IDENTITY | IN | INDICATOR | INNER | INOUT | INSENSITIVE | INSERT | INT | INTEGER | INTERSECT | INTERSECTION | INTERVAL | INTO | IS | JOIN | LANGUAGE | LARGE | LATERAL | LEADING | LEFT | LIKE | LN | LOCAL | LOCALTIME | LOCALTIMESTAMP | LOWER | MATCH | MAX | MEMBER | MERGE | METHOD | MIN | MINUTE | MOD | MODIFIES | MODULE | MONTH | MULTISET | NATIONAL | NATURAL | NCHAR | NCLOB | NEW | NO | NONE | NORMALIZE | NOT | NULL | NULLIF | NUMERIC | OCTET_LENGTH | OF | OLD | ON | ONLY | OPEN | OR | ORDER | OUT | OUTER | OVER | OVERLAPS | OVERLAY | PARAMETER | PARTITION | PERCENT_RANK | PERCENTILE_CONT | PERCENTILE_DISC | POSITION | POWER | PRECISION | PREPARE | PRIMARY | PROCEDURE | RANGE | RANK | READS | REAL | RECURSIVE | REF | REFERENCES | REFERENCING | REGR_AVGX | REGR_AVGY | REGR_COUNT | REGR_INTERCEPT | REGR_R2 | REGR_SLOPE | REGR_SXX | REGR_SXY | REGR_SYY | RELEASE | RESULT | RETURN | RETURNS | REVOKE | RIGHT | ROLLBACK | ROLLUP | ROW | ROW_NUMBER | ROWS | SAVEPOINT | SCOPE | SCROLL | SEARCH | SECOND | SELECT | SENSITIVE | SESSION_USER | SET | SIMILAR | SMALLINT | SOME | SPECIFIC | SPECIFICTYPE | SQL | SQLEXCEPTION | SQLSTATE | SQLWARNING | SQRT | START | STATIC | STDDEV_POP | STDDEV_SAMP | SUBMULTISET | SUBSTRING | SUM | SYMMETRIC | SYSTEM | SYSTEM_USER | TABLE | TABLESAMPLE | THEN | TIME | TIMESTAMP | TIMEZONE_HOUR | TIMEZONE_MINUTE | TO | TRAILING | TRANSLATE | TRANSLATION | TREAT | TRIGGER | TRIM | TRUE | UESCAPE | UNION | UNIQUE | UNKNOWN | UNNEST | UPDATE | UPPER | USER | USING | VALUE | VALUES | VAR_POP | VAR_SAMP | VARCHAR | VARYING | WHEN | WHENEVER | WHERE | WIDTH_BUCKET | WINDOW | WITH | WITHIN | WITHOUT | YEAR > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Mike Mascari > Sent: Wednesday, August 18, 2004 11:24 AM > To: Ying Lu > Cc: [EMAIL PROTECTED] > Subject: Re: [GENERAL] Could not create a table named "USER" > under postgreSQL > > > 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: > > [EMAIL PROTECTED] create table user (key integer); > ERROR: syntax error at or near "user" at character 14 > [EMAIL PROTECTED] create table "user" (key integer); CREATE TABLE > [EMAIL PROTECTED] insert into user values (1); > ERROR: syntax error at or near "user" at character 13 > [EMAIL PROTECTED] insert into "user" values (1); > > HTH, > > Mike Mascari > > > ---(end of > broadcast)--- > TIP 7: don't forget to increase your free space map settings > ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Could not create a table named "USER" under postgreSQL
Thanks a lot. That is what I am looking for :) Emi Dann Corbit wrote: According to the current SQL language ANSI/ISO standard, the following are reserved words: ::= ABS | ALL | ALLOCATE | ALTER | AND | ANY | ARE | ARRAY | AS | ASENSITIVE | ASYMMETRIC | AT | ATOMIC | AUTHORIZATION | AVG | BEGIN | BETWEEN | BIGINT | BINARY | BLOB | BOOLEAN | BOTH | BY | CALL | CALLED | CARDINALITY | CASCADED | CASE | CAST | CEIL | CEILING | CHAR | CHAR_LENGTH | CHARACTER | CHARACTER_LENGTH | CHECK | CLOB | CLOSE | COALESCE | COLLATE | COLLECT | COLUMN | COMMIT | CONDITION | CONNECT | CONSTRAINT | CONVERT | CORR | CORRESPONDING | COUNT | COVAR_POP | COVAR_SAMP | CREATE | CROSS | CUBE | CUME_DIST | CURRENT | CURRENT_DATE | CURRENT_DEFAULT_TRANSFORM_GROUP | CURRENT_PATH | CURRENT_ROLE | CURRENT_TIME | CURRENT_TIMESTAMP | CURRENT_TRANSFORM_GROUP_FOR_TYPE | CURRENT_USER | CURSOR | CYCLE | DATE | DAY | DEALLOCATE | DEC | DECIMAL | DECLARE | DEFAULT | DELETE | DENSE_RANK | DEREF | DESCRIBE | DETERMINISTIC | DISCONNECT | DISTINCT | DOUBLE | DROP | DYNAMIC | EACH | ELEMENT | ELSE | END | END-EXEC | ESCAPE | EVERY | EXCEPT | EXEC | EXECUTE | EXISTS | EXP | EXTERNAL | EXTRACT | FALSE | FETCH | FILTER | FLOAT | FLOOR | FOR | FOREIGN | FREE | FROM | FULL | FUNCTION | FUSION | GET | GLOBAL | GRANT | GROUP | GROUPING | HAVING | HOLD | HOUR | IDENTITY | IN | INDICATOR | INNER | INOUT | INSENSITIVE | INSERT | INT | INTEGER | INTERSECT | INTERSECTION | INTERVAL | INTO | IS | JOIN | LANGUAGE | LARGE | LATERAL | LEADING | LEFT | LIKE | LN | LOCAL | LOCALTIME | LOCALTIMESTAMP | LOWER | MATCH | MAX | MEMBER | MERGE | METHOD | MIN | MINUTE | MOD | MODIFIES | MODULE | MONTH | MULTISET | NATIONAL | NATURAL | NCHAR | NCLOB | NEW | NO | NONE | NORMALIZE | NOT | NULL | NULLIF | NUMERIC | OCTET_LENGTH | OF | OLD | ON | ONLY | OPEN | OR | ORDER | OUT | OUTER | OVER | OVERLAPS | OVERLAY | PARAMETER | PARTITION | PERCENT_RANK | PERCENTILE_CONT | PERCENTILE_DISC | POSITION | POWER | PRECISION | PREPARE | PRIMARY | PROCEDURE | RANGE | RANK | READS | REAL | RECURSIVE | REF | REFERENCES | REFERENCING | REGR_AVGX | REGR_AVGY | REGR_COUNT | REGR_INTERCEPT | REGR_R2 | REGR_SLOPE | REGR_SXX | REGR_SXY | REGR_SYY | RELEASE | RESULT | RETURN | RETURNS | REVOKE | RIGHT | ROLLBACK | ROLLUP | ROW | ROW_NUMBER | ROWS | SAVEPOINT | SCOPE | SCROLL | SEARCH | SECOND | SELECT | SENSITIVE | SESSION_USER | SET | SIMILAR | SMALLINT | SOME | SPECIFIC | SPECIFICTYPE | SQL | SQLEXCEPTION | SQLSTATE | SQLWARNING | SQRT | START | STATIC | STDDEV_POP | STDDEV_SAMP | SUBMULTISET | SUBSTRING | SUM | SYMMETRIC | SYSTEM | SYSTEM_USER | TABLE | TABLESAMPLE | THEN | TIME | TIMESTAMP | TIMEZONE_HOUR | TIMEZONE_MINUTE | TO | TRAILING | TRANSLATE | TRANSLATION | TREAT | TRIGGER | TRIM | TRUE | UESCAPE | UNION | UNIQUE | UNKNOWN | UNNEST | UPDATE | UPPER | USER | USING | VALUE | VALUES | VAR_POP | VAR_SAMP | VARCHAR | VARYING | WHEN | WHENEVER | WHERE | WIDTH_BUCKET | WINDOW | WITH | WITHIN | WITHOUT | YEAR -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Mike Mascari Sent: Wednesday, August 18, 2004 11:24 AM To: Ying Lu Cc: [EMAIL PROTECTED] Subject: Re: [GENERAL] Could not create a table named "USER" under postgreSQL 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: [EMAIL PROTECTED] create table user (key integer); ERROR: syntax error at or near "user" at character 14 [EMAIL PROTECTED] create table "user" (key integer); CREATE TABLE [EMAIL PROTECTED] insert into user values (1); ERROR: syntax error at or near "user" at character 13 [EMAIL PROTECTED] insert into "user" values (1); HTH, Mike Mascari ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Could not create a table named "USER" under postgreSQL
"Ying Lu" <[EMAIL PROTECTED]> wrote: > > I have a question about "date" & "timestamp" types in PostgreSQL. I want > to setup the default value '-00-00' and "-00-00 00:00:00" for > them. However, it seems that PostgreSQL does not support it. Could > someone helps me please? if you want to store invalid dates and timestamps, you are better off using varchar. if you just want to store a null-value, use NULL does mysql really allow you to store these values? gnari ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Could not create a table named "USER" under postgreSQL
Are you aware that there is NO zero year? The common era starts with the year 1 AD. There is also no zero month, and there is no zero day. All three parts of your date are hence invalid. E.g. the date -00-00 does not exist, and neither does 0001-00-00 or -01-00 etc. If you are determined to insert bad data into these fields, you could make them character. Or you could choose a valid date as the default. > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Ying Lu > Sent: Wednesday, August 18, 2004 11:36 AM > To: [EMAIL PROTECTED] > Subject: [GENERAL] Could not create a table named "USER" > under postgreSQL > > > Hello, > > I have a question about "date" & "timestamp" types in > PostgreSQL. I want > to setup the default value '-00-00' and "-00-00 00:00:00" for > them. However, it seems that PostgreSQL does not support it. Could > someone helps me please? > > The example table: > > T1 (col1 varchar(7) not null, > col2 varchar(4) not null, > col3 date not null, > col 4 varchar(3), > primary key(col1, col2, col3) > ) > > In my design model, "col3" has to be one of the primary key > part. Since > at the beginning of the data population, we do not know the value of > "col3"; values for "col3" are input throught GUI. Therefore, > when I use > MySQL, the default values I gave is "-00-00". However, after I > migrate to postgreSQL, I could not setup the default values as > "-00-00" any more. Could somebody help me about it > please? I'd like > to know how I can save '-00-00' as the default value for > "date" and > "timestamp" types. > > By the way, I also tried "my2pg.pl" to migrate table > structures got by > mysqldump to postgreSQL. The places I have '000-00-00' have > been changed > to '0001-01-01' by this perl script. Remarkably clever to repair all those defects to something remotely sensible. Whoever wrote that script, I give an 'A+'. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
