From: cadiolis@gmail.com
To: pgsql-sql@postgresql.org
Subject: Re: Text->Date conversion in a WHERE clause
Date: 12 Oct 2005 14:37:56 -0700
Message-ID: <1129153075.962155.131260@g43g2000cwa.googlegroups.com> (raw)
References: <df01c91b0510121359m1d7e67b5n41ee4d939798b69b@mail.gmail.com>
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
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: cadiolis@gmail.com
Subject: Re: Text->Date conversion in a WHERE clause
In-Reply-To: <1129153075.962155.131260@g43g2000cwa.googlegroups.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