To: Collin Peters <cadiolis@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Text->Date conversion in a WHERE clause
References: <df01c91b0510121359m1d7e67b5n41ee4d939798b69b@mail.gmail.com>
In-Reply-To: <df01c91b0510121359m1d7e67b5n41ee4d939798b69b@mail.gmail.com>
From: Greg Stark <gsstark@mit.edu>
Organization: The Emacs Conspiracy; member since 1992
Date: 12 Oct 2005 18:01:18 -0400
Message-ID: <8764s21jkh.fsf@stark.xeocode.com>
Lines: 37
User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.4
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

Collin Peters <cadiolis@gmail.com> writes:

> I have a table that has some columns which store 'custom' fields so the
> content varies according to the user that the row belongs to.  For one
> of the groups of users the field is a date (the type of the field is
> 'text' though).  I'm trying to perform a query where it only returns
> values in a certain date range so in the WHERE clause I have
> 
> WHERE cust3 <> ''
> AND cust3::text::timestamp > CURRENT_DATE - interval '1 month'
> 
> This results in the error 'ERROR:  date/time field value out of range:


> This results in the error 'ERROR:  date/time field value out of range:
> "052-44-5863"'.  Now that is obviously not a valid date.... but there
> is actually more to the where clause and the first part of it excludes
> all rows where the user is not even the correct type, so the row which
> includes the field '052-44-5863' should really not even be checked.

I think you have to use a CASE expression like:

WHERE CASE WHEN user_type = 1
           THEN cust3::timestamp > CURRENT_DATE - interval '1 month' 
           ELSE false 
       END CASE
  AND ...


There's no advantage to doing this kind of thing though. Good database design
principles dictate having one column for each piece of data. Just leave the
columns for which the data is inappropriate NULL. NULLs take effectively no
space.

-- 
greg