Re: How to setup default value "0000-00-00" for "date"

Emi Lu wrote:
> Hello all,
>
> 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?

PostgreSQL doesn't and almost certainly never will support "0000-00-00"
as a date. That's because it isn't a valid date. You also can't store
13.723, "Hello world" or (12,13) in a date column either.

Where you don't have a valid date to store you should use NULL. This
business of storing zeroes is a horrible MySQL design mistake.

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

If you don't know the value of col3, it can't be part of your primary
key. That's part of the definition of "primary key" and trying to work
around it is what's causing you problems here.

If you have an initial population of data that then needs to have "col3"
set then it sounds like you need two tables T0 with primary-key
(col1,col2) and T1 with (col1,col2,col3) and copy from T0=>T1 as users
supply values. Difficult to say without knowing more about your situation.

HTH
--
Richard Huxton
Archonet Ltd