I found a solution
you are right tom with the NULLs but I think I found an elegant solution to this

First of all I patch the date_in function like this
--- postgresql-6.5.2/src/backend/utils/adt/datetime.c	Mon Aug  2 07:24:51 1999
+++ postgresql-6.5.2-new/src/backend/utils/adt/datetime.c	Thu Mar  2 00:55:54 2000
@@ -51,8 +51,15 @@
 #ifdef DATEDEBUG
 	printf("date_in- input string is %s\n", str);
 #endif
-	if ((ParseDateTime(str, lowstr, field, ftype, MAXDATEFIELDS, &nf) != 0)
-	 || (DecodeDateTime(field, ftype, nf, &dtype, tm, &fsec, &tzp) != 0))
+	if (strlen(str) == 0 ) {
+	    tm->tm_year = 9999;
+	    tm->tm_mon = 1;
+	    tm->tm_mday = 1;
+	    dtype = DTK_DATE;
+	}
+	else
+	    if ((ParseDateTime(str, lowstr, field, ftype, MAXDATEFIELDS, &nf) != 0)
+	     || (DecodeDateTime(field, ftype, nf, &dtype, tm, &fsec, &tzp) != 0))
 		elog(ERROR, "Bad date external representation '%s'", str);
 
 	switch (dtype)

and the I write some functions (used in triggers) to convert the date 9999/01/01 to nulls.
like this

create function check_date(date) returns date as '
    begin
        if $1 = ''9999-01-01''::date then
            return NULL;
        else
            return $1;
        end if;
    end;
' language 'plpgsql';

create function check_peo_dates () returns opaque as '
    begin
        NEW.PEO_MAIL =  check_date(NEW.PEO_MAIL);
        NEW.PEO_VISIT = check_date(NEW.PEO_VISIT);
        NEW.PEO_SP =    check_date(NEW.PEO_SP);
        NEW.PEO_VAL =   check_date(NEW.PEO_VAL);
        NEW.PEO_CRE =   check_date(NEW.PEO_CRE);
        return NEW;
    end;
' language 'plpgsql';

is this a good solution or is it plain dumb.
The only problem with this is you need to write triggers for all tables that have date fields.

What is the SQL92 rule about emty dates? Does anyone know?

Willy De la Court [SMTP:Willy.DelaCourt@pandora.be] wrote:
> > Tom Lane [SMTP:tgl@sss.pgh.pa.us] wrote:
> > Willy De la Court <Willy.DelaCourt@pandora.be> writes:
> > > I want to change the default behaviour of the data_in function without
> > > recompiling postgres.  the functionality is the following.
> > 
> > > create table test (d date);
> > > insert into test values ('');
> > 
> > > this should result in the field d containing NULL and the insert
> > > command should work without returning an error.
> > 
> > Not possible at present, since a datatype's typinput function can't
> > return a NULL.  I suppose it will be possible after we redo the
> > function manager interface, but in any case you'd have no hope of
> > changing the behavior "without recompiling postgres".



Thu, 2 Mar 2000 01:49:55 +0100
Willy De la Court <Willy.DelaCourt@pandora.be>
RE: [HACKERS] empty dates and changing the default date behaviour 
Willy De la Court <Willy.DelaCourt@pandora.be> writes:
> you are right tom with the NULLs but I think I found an elegant
> solution to this

Interesting; I didn't realize that plpgsql supported functions returning
NULLs.

I wouldn't call it an "elegant" solution, by any means ;-).  But if it
gets the job done for you, it'll do as a stopgap until datein() can
return a NULL itself.

			regards, tom lane

Wed, 01 Mar 2000 22:51:42 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] empty dates and changing the default date behaviour 