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: [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 '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
