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+'.