Re: Validity check in to_date?

T've been following this thread with interest because I have a related
problem. Basically we are storing dates in CHAR fields with al the
associated problems. I'd like to do it, but changing everything to date
fields isn't practical for now, so as a stopgap solution, I want to provide
some validation at the database level.

I tried:

create domain ymdtest2 as char(10) constraint valid_date check
(VALUE::DATE);

But it gives this error:

ERROR: cannot cast type character to date

I also tried:

create domain test char(10) check (CAST(VALUE AS DATE));

and it gives the same error. I don't need to actually modify VALUE, but I'd
like to have it so that any attempt to insert an invalid date will cause the
transaction to fail. This is OK:

SELECT CAST('2003-3-31' AS DATE);

So it is possible to cast type character to date (as we all know) so it
seems that the problem only applies to checks.

Now, I am wondering if there is a clever work around to this? It escapes me
for now anyway.
Regards
Iain

----- Original Message -----
From: "Karel Zak" <zakkr(at)zf(dot)jcu(dot)cz>
To: "Christoph Haller" <ch(at)rodos(dot)fzk(dot)de>
Cc: "Alexander M. Pravking" <fduch(at)antar(dot)bryansk(dot)ru>;
<pgsql-sql(at)postgresql(dot)org>
Sent: Tuesday, December 02, 2003 6:57 PM
Subject: Re: [SQL] Validity check in to_date?

> On Tue, Dec 02, 2003 at 10:27:04AM +0100, Christoph Haller wrote:
> > >
> > > I just discovered that to_date() function does not check if supplied
> > > date is correct, giving surprising (at least for me) results:
> > >
> > > fduch=# SELECT to_date('31.11.2003', 'DD.MM.YYYY');
> > > to_date
> > > ------------
> > > 2003-12-01
> > >
> > > or even
> > >
> > > fduch=# SELECT to_date('123.45.2003', 'DD.MM.YYYY');
> > > to_date
> > > ------------
> > > 2007-01-03
> > >
> > > to_timestamp() seems to work the same way. It's probably useful
sometimes,
> > > but not in my case... Is it how it supposed to work?
> > > If so, how can I do such a validity check?
> > > If not, has something changed in 7.4?
>
> No change in 7.4. Maybe in 7.5 or in some 7.4.x.
>
> > As far as I know these results are correct in terms of the underlying
> > C-library function mktime(). This function is intended to be used when
> > adding/subtracting intervals from a given timestamp.
> > I don't know of any postgres function doing the check you're looking
for.
> > But I can't believe this is the first time this topic is brought up.
> > You may search the archives on "date plausibility" are related terms.
>
> The others PostgreSQL stuff which full parse (means check ranges)
> date/time is less optimistic with this:
>
> # select '31.11.2003'::date;
> ERROR: date/time field value out of range: "31.11.2003"
>
> Karel
>
> --
> Karel Zak <zakkr(at)zf(dot)jcu(dot)cz>
> http://home.zf.jcu.cz/~zakkr/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster