From: Greg Stark <gsstark@mit.edu>
To: Collin Peters <cadiolis@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Text->Date conversion in a WHERE clause
Date: 12 Oct 2005 18:01:18 -0400
Message-ID: <8764s21jkh.fsf@stark.xeocode.com> (raw)
In-Reply-To: <df01c91b0510121359m1d7e67b5n41ee4d939798b69b@mail.gmail.com>
References: <df01c91b0510121359m1d7e67b5n41ee4d939798b69b@mail.gmail.com>
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
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: gsstark@mit.edu, cadiolis@gmail.com
Subject: Re: Text->Date conversion in a WHERE clause
In-Reply-To: <8764s21jkh.fsf@stark.xeocode.com>
* 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