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
>
>