Thread: 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 '0000-00-00' and "0000-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 "0000-00-00". However, after I
migrate to postgreSQL, I could not setup the default values as
"0000-00-00" any more. Could somebody help me about it please? I'd like
to know how I can save '0000-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
According to the current SQL language ANSI/ISO standard, the following are reserved words: <reserved word> ::= 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: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Mike Mascari > Sent: Wednesday, August 18, 2004 11:24 AM > To: Ying Lu > Cc: pgsql-general@postgresql.org > 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: > > [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 >
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: > ><reserved word> ::= >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: pgsql-general-owner@postgresql.org >>[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Mike Mascari >>Sent: Wednesday, August 18, 2004 11:24 AM >>To: Ying Lu >>Cc: pgsql-general@postgresql.org >>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: >> >>[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 >> >> >>
[As a note, it's a bad idea to put a new message with a new problem in the same thread.] On Wed, 18 Aug 2004, Ying Lu wrote: > I have a question about "date" & "timestamp" types in PostgreSQL. I want > to setup the default value '0000-00-00' and "0000-00-00 00:00:00" for > them. However, it seems that PostgreSQL does not support it. Could Right, because those aren't valid values for those types. I think you have to choose between using a date (or timestamp) column and constraining the values to valid ones (for example, possibly '0001-01-01') or using a type that doesn't constrain the value to valid dates.
"Ying Lu" <ying_lu@cs.concordia.ca> wrote: > > I have a question about "date" & "timestamp" types in PostgreSQL. I want > to setup the default value '0000-00-00' and "0000-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
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 0000-00-00 does not exist, and neither does 0001-00-00 or 0000-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: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Ying Lu > Sent: Wednesday, August 18, 2004 11:36 AM > To: pgsql-general@postgresql.org > 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 '0000-00-00' and "0000-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 "0000-00-00". However, after I > migrate to postgreSQL, I could not setup the default values as > "0000-00-00" any more. Could somebody help me about it > please? I'd like > to know how I can save '0000-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+'.
All right. I will think of using either "0001-01-01" or changing the column type to varchar(n). To Stephan: I had planed to give a new thread name, but I pasted the wrong subject name, using the previous one :( Thanks a lot, Emi Dann Corbit wrote: >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 >0000-00-00 does not exist, and neither does 0001-00-00 or 0000-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: pgsql-general-owner@postgresql.org >>[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Ying Lu >>Sent: Wednesday, August 18, 2004 11:36 AM >>To: pgsql-general@postgresql.org >>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 '0000-00-00' and "0000-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 "0000-00-00". However, after I >>migrate to postgreSQL, I could not setup the default values as >>"0000-00-00" any more. Could somebody help me about it >>please? I'd like >>to know how I can save '0000-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 > >
Ying Lu <ying_lu@cs.concordia.ca> writes:
> 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 "0000-00-00".
Use NULL. That's what it's for.
regards, tom lane
Stephan Szabo wrote:
> [As a note, it's a bad idea to put a new message with a new problem in the
> same thread.]
>
> On Wed, 18 Aug 2004, Ying Lu wrote:
>
>>I have a question about "date" & "timestamp" types in PostgreSQL. I want
>>to setup the default value '0000-00-00' and "0000-00-00 00:00:00" for
>>them. However, it seems that PostgreSQL does not support it. Could
>
> Right, because those aren't valid values for those types. I think you
> have to choose between using a date (or timestamp) column and constraining
> the values to valid ones (for example, possibly '0001-01-01') or using a
> type that doesn't constrain the value to valid dates.
If it is truly unknown data, then how about two relations:
-- Known set of [col1 - col2]'s
T1 (col1 varchar(7) not null,
col2 varchar(4) not null,
col4 varchar(3) not null,
primary key (col1, col2));
-- Known set of dates of [col1 - col2]'s
T2 (col1 varchar(7) not null,
col2 varchar(4) not null,
col3 date not null,
primary key (col1, col2),
foreign key (col1, col2)
references T1 (col1, col2) on delete cascade
);
No NULLs and fully normalized. Create a view with outer joins as
appropriate.
Mike Mascari
On Wed, Aug 18, 2004 at 15:03:02 -0400, Ying Lu <ying_lu@cs.concordia.ca> wrote: > > To Stephan: I had planed to give a new thread name, but I pasted the > wrong subject name, using the previous one :( Even if you change the subject, you still should start a new thread (i.e. don't reply to an existing thread).