Thread: Text->Date conversion in a WHERE clause

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:
"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.

My main confusion lies in the assumption I made that the offending row
would not even be included as it should have already been discarded.
Is this not the case?    How can I overcome this problem?  There
appears to be no isDate() function in postgresql like there is in sql
server.

Regards,
Collin Peters

As an example:

CREATE TABLE userdata (   userdata_id serial NOT NULL,   user_id smallint,   data text
);

CREATE TABLE users (   user_id serial NOT NULL,   name text,   "type" smallint
);

INSERT INTO userdata (userdata_id, user_id, data) VALUES (1, 1,
'2005-01-01');
INSERT INTO userdata (userdata_id, user_id, data) VALUES (2, 2,
'2005-10-10');
INSERT INTO userdata (userdata_id, user_id, data) VALUES (3, 3,
'052-44-5863');
INSERT INTO userdata (userdata_id, user_id, data) VALUES (4, 4,
'052-44-5863');

INSERT INTO users (user_id, name, "type") VALUES (1, 'Jim', 1);
INSERT INTO users (user_id, name, "type") VALUES (2, 'John', 1);
INSERT INTO users (user_id, name, "type") VALUES (3, 'Bob', 2);
INSERT INTO users (user_id, name, "type") VALUES (4, 'Bill', 2);


Then run the query:
SELECT *
FROM
(SELECT u.user_id, ud.dataFROM users u, userdata udWHERE u.user_id = ud.user_idAND u.type = 1
) subusers
WHERE subusers.data::text::date <  now();

Returns the message: ERROR:  date/time field value out of range:
"052-44-5863"

So my question is how does this query ever even SEE the row containing
"052-44-5863"?  The sub-query doesn't return that row so I don't see
how it can get this error.

Regards,
Collin Peters

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

Collin Peters <cadiolis@gmail.com> writes:
> WHERE cust3 <> ''
> AND cust3::text::timestamp > CURRENT_DATE - interval '1 month'

> 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.

Your mistake is in supposing that WHERE clauses are guaranteed to be
evaluated in a particular order.  Such guarantees are made only for a
very few specific constructs such as CASE.  See
http://www.postgresql.org/docs/8.0/static/sql-expressions.html#SYNTAX-EXPRESS-EVAL
        regards, tom lane

cadiolis@gmail.com writes:
> SELECT *
> FROM
> (
>     SELECT u.user_id, ud.data
>     FROM users u, userdata ud
>     WHERE u.user_id = ud.user_id
>     AND u.type = 1
> ) subusers
> WHERE subusers.data::text::date <  now();

> So my question is how does this query ever even SEE the row containing
> "052-44-5863"?  The sub-query doesn't return that row so I don't see
> how it can get this error.

BTW, the fallacy in this idea is that the planner pushes WHERE clauses
as far down the plan tree as it can.  EXPLAIN would show you the actual
plan tree, but it's probably along the lines of
Join using u.user_id = ud.user_id    Scan users u where u.type = 1    Scan userdata ud where ud.data::text::date <
now();

If we did not do this, it would pretty much cripple the performance
of queries involving views (since a view is nothing but a macro for a
sub-select).
        regards, tom lane