From: Iain <iain@mst.co.jp>
To: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Alexander M. Pravking <fduch@antar.bryansk.ru>
Cc: pgsql-sql@postgresql.org
Subject: Re: Validity check in to_date?
Date: Wed, 3 Dec 2003 11:18:04 +0900
Message-ID: <007201c3b943$aeeeaa50$7201a8c0@mst1x5r347kymb> (raw)
References: <20031127141125.GC87728@dyatel.antar.bryansk.ru>
<200312020927.KAA17321@rodos>
<20031202095713.GB17884@zf.jcu.cz>
<005201c3b8c1$52374a60$7201a8c0@mst1x5r347kymb>
<20031202081242.M87630@megazone.bigpanda.com>
Sweeet!
This is what I ended up with:
create domain testdate char(10) check (VALUE::text::date = VALUE);
(it wasn't possible to insert a NULL date with (VALUE::text::date IS NOT
NULL); )
I'm pretty happy with this as I didn't even have to use
to_char(VALUE::text::date , 'YYYY-MM-DD') for the comparison, since the
date format I am using matches the "datestyle" setting.
It seems to accept any valid date, as well as NULL, while rejecting inputs
such as:
insert into test1 values ('2002-03-32');
insert into test1 values ('200-03-22');
insert into test1 values ('2002- 3-22');
insert into test1 values ('2002-03-2');
insert into test1 values ('2002-03- 2');
insert into test1 values ('2002-3-2');
insert into test1 values ('2002-14-02');
insert into test1 values ('');
insert into test1 values ('2002/03/22');
insert into test1 values ('2002/03/32');
insert into test1 values ('200/03/22');
insert into test1 values ('2002/ 3/22');
insert into test1 values ('2002/03/2');
insert into test1 values ('2002/03/ 2');
insert into test1 values ('2002/3/2');
insert into test1 values ('2002/14/02');
It's no silk purse, but it's short and sweet and I'm satisfied.
Thanks guys.
Rregards
Iain
----- Original Message -----
From: "Stephan Szabo" <sszabo@megazone.bigpanda.com>
To: "Iain" <iain@mst.co.jp>
Cc: "Karel Zak" <zakkr@zf.jcu.cz>; "Christoph Haller" <ch@rodos.fzk.de>;
"Alexander M. Pravking" <fduch@antar.bryansk.ru>; <pgsql-sql@postgresql.org>
Sent: Wednesday, December 03, 2003 1:15 AM
Subject: Re: [SQL] Validity check in to_date?
> On Tue, 2 Dec 2003, Iain wrote:
>
> > 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));
>
> I'd try CAST(CAST(VALUE AS TEXT) AS DATE)
>
> There's a text->date conversion, but not one from character(n).
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
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, sszabo@megazone.bigpanda.com, fduch@antar.bryansk.ru
Subject: Re: Validity check in to_date?
In-Reply-To: <007201c3b943$aeeeaa50$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