X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id B4C54D1B518 for ; Wed, 3 Dec 2003 02:18:53 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 39106-05 for ; Tue, 2 Dec 2003 22:18:30 -0400 (AST) Received: from millenium.mst.co.jp (unknown [210.230.185.241]) by svr1.postgresql.org (Postfix) with ESMTP id 3588AD1B439 for ; Tue, 2 Dec 2003 22:18:21 -0400 (AST) Received: from mst1x5r347kymb (lc12114 [192.168.1.114]) by millenium.mst.co.jp (8.11.6p2/3.7W) with SMTP id hB328Yi02628; Wed, 3 Dec 2003 11:08:34 +0900 Message-ID: <007201c3b943$aeeeaa50$7201a8c0@mst1x5r347kymb> From: "Iain" To: "Stephan Szabo" , "Alexander M. Pravking" Cc: References: <20031127141125.GC87728@dyatel.antar.bryansk.ru> <200312020927.KAA17321@rodos> <20031202095713.GB17884@zf.jcu.cz> <005201c3b8c1$52374a60$7201a8c0@mst1x5r347kymb> <20031202081242.M87630@megazone.bigpanda.com> Subject: Re: Validity check in to_date? Date: Wed, 3 Dec 2003 11:18:04 +0900 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/25 X-Sequence-Number: 16191 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" To: "Iain" Cc: "Karel Zak" ; "Christoph Haller" ; "Alexander M. Pravking" ; 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