pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedText->Date conversion in a WHERE clause
5+ messages / 4 participants
[nested] [flat]
* Text->Date conversion in a WHERE clause
@ 2005-10-12 20:59 Collin Peters <cadiolis@gmail.com>
2005-10-12 21:37 ` Re: Text->Date conversion in a WHERE clause cadiolis@gmail.com
2005-10-12 22:01 ` Re: Text->Date conversion in a WHERE clause Greg Stark <gsstark@mit.edu>
2005-10-12 22:20 ` Re: Text->Date conversion in a WHERE clause Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 3 replies; 5+ messages in thread
From: Collin Peters @ 2005-10-12 20:59 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Text->Date conversion in a WHERE clause
2005-10-12 20:59 Text->Date conversion in a WHERE clause Collin Peters <cadiolis@gmail.com>
@ 2005-10-12 21:37 ` cadiolis@gmail.com
2005-10-12 23:00 ` Re: Text->Date conversion in a WHERE clause Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 1 reply; 5+ messages in thread
From: cadiolis@gmail.com @ 2005-10-12 21:37 UTC (permalink / raw)
To: pgsql-sql
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.data
FROM users u, userdata ud
WHERE u.user_id = ud.user_id
AND 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Text->Date conversion in a WHERE clause
2005-10-12 20:59 Text->Date conversion in a WHERE clause Collin Peters <cadiolis@gmail.com>
2005-10-12 21:37 ` Re: Text->Date conversion in a WHERE clause cadiolis@gmail.com
@ 2005-10-12 23:00 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2005-10-12 23:00 UTC (permalink / raw)
To: cadiolis@gmail.com; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Text->Date conversion in a WHERE clause
2005-10-12 20:59 Text->Date conversion in a WHERE clause Collin Peters <cadiolis@gmail.com>
@ 2005-10-12 22:01 ` Greg Stark <gsstark@mit.edu>
2 siblings, 0 replies; 5+ messages in thread
From: Greg Stark @ 2005-10-12 22:01 UTC (permalink / raw)
To: Collin Peters <cadiolis@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Text->Date conversion in a WHERE clause
2005-10-12 20:59 Text->Date conversion in a WHERE clause Collin Peters <cadiolis@gmail.com>
@ 2005-10-12 22:20 ` Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2005-10-12 22:20 UTC (permalink / raw)
To: Collin Peters <cadiolis@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-10-12 23:00 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-10-12 20:59 Text->Date conversion in a WHERE clause Collin Peters <cadiolis@gmail.com>
2005-10-12 21:37 ` cadiolis@gmail.com
2005-10-12 23:00 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-10-12 22:01 ` Greg Stark <gsstark@mit.edu>
2005-10-12 22:20 ` Tom Lane <tgl@sss.pgh.pa.us>
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