Databasen - NULL dates

[Toby Corkindale]

On Wed, Oct 18, 2006 at 03:35:43PM +0100, David Cantrell wrote:
> On Wed, Oct 18, 2006 at 01:55:00PM +0100, Andy Armstrong wrote:
> > On 18 Oct 2006, at 13:17, Jurgen Pletinckx wrote:
> > >I can see where I would be interested in the difference between
> > >as-yet-unknown (will be filled in during one of the next rounds
> > >of data entry) and unknowable (won't ever be filled in, barring
> > >paradigm shift or policy change).
> > >NULL and VERY NULL?
> > One NULL is never enough - just like undef :)
> > If you want to know the reason for nullness that could go in another  
> > column - or perhaps another table.
> 
> CREATE TABLE fucked_up (
>   date_field_is        ENUM('DATE', 'NULL', 'VERY NULL', 'IMAGINARY',
>                             'FICTIONAL', 'APPROXIMATE', 'WRONG', ...),
>   date_field           VARCHAR(16),
>   ...
> );
> 
> May the database gods strike me down for even considering that.

Or to refine your suggestion, by smoking more crack:

CREATE TYPE dateCrack AS (
    "date"  DATE,
    status  ENUM('DATE','NULL','VERY NULL', etc...)
);

CREATE TABLE foo AS (
    id SERIAL PRIMARY KEY,
    name TEXT,
    birthday DATECRACK
);

INSERT INTO foo (name,birthday) VALUES ('WHotsit', ROW('1970-01-01','WRONG'));

tjc

-- 
Turning and turning in the widening gyre/The falcon cannot hear the falconer;
Things fall apart, the centre cannot hold/Mere anarchy is loosed upon the world
(gpg --keyserver www.co.uk.pgp.net --recv-key B1CCF88E)