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 EACDCD1D23B for ; Tue, 2 Dec 2003 16:16:05 +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 44546-04 for ; Tue, 2 Dec 2003 12:15:39 -0400 (AST) Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by svr1.postgresql.org (Postfix) with ESMTP id 10FD6D1D3FB for ; Tue, 2 Dec 2003 12:15:35 -0400 (AST) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 8EA0735494; Tue, 2 Dec 2003 08:15:24 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 8D04035493; Tue, 2 Dec 2003 08:15:24 -0800 (PST) Date: Tue, 2 Dec 2003 08:15:24 -0800 (PST) From: Stephan Szabo To: Iain Cc: Karel Zak , Christoph Haller , "Alexander M. Pravking" , pgsql-sql@postgresql.org Subject: Re: Validity check in to_date? In-Reply-To: <005201c3b8c1$52374a60$7201a8c0@mst1x5r347kymb> Message-ID: <20031202081242.M87630@megazone.bigpanda.com> References: <20031127141125.GC87728@dyatel.antar.bryansk.ru> <200312020927.KAA17321@rodos> <20031202095713.GB17884@zf.jcu.cz> <005201c3b8c1$52374a60$7201a8c0@mst1x5r347kymb> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/21 X-Sequence-Number: 16187 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).