Re: Date and Timestamps

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