From: Iain <iain@mst.co.jp>
To: Karel Zak <zakkr@zf.jcu.cz>
To: Christoph Haller <ch@rodos.fzk.de>
Cc: Alexander M. Pravking <fduch@antar.bryansk.ru>
Cc: pgsql-sql@postgresql.org
Subject: Re: Validity check in to_date?
Date: Tue, 2 Dec 2003 19:44:54 +0900
Message-ID: <005201c3b8c1$52374a60$7201a8c0@mst1x5r347kymb> (raw)
References: <20031127141125.GC87728@dyatel.antar.bryansk.ru>
<200312020927.KAA17321@rodos>
<20031202095713.GB17884@zf.jcu.cz>
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@zf.jcu.cz>
To: "Christoph Haller" <ch@rodos.fzk.de>
Cc: "Alexander M. Pravking" <fduch@antar.bryansk.ru>;
<pgsql-sql@postgresql.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@zf.jcu.cz>
> http://home.zf.jcu.cz/~zakkr/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: iain@mst.co.jp, zakkr@zf.jcu.cz, ch@rodos.fzk.de, fduch@antar.bryansk.ru
Subject: Re: Validity check in to_date?
In-Reply-To: <005201c3b8c1$52374a60$7201a8c0@mst1x5r347kymb>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox