thanks for the help.
between CAST(? as timestamp) and CAST(? as timestamp) fixes my problem.
because my second query uses a between the same day parameter it wasn't
being found when it was treated as a string, as a timestamp it works
fine. Apparently a simple query will convert the string to a timestamp
correctly, while they way I was doing things in java was forcing it to
be a string, thus killing my query.
I'm using a rather dense index:
CREATE INDEX stat_speed_big ON aggregationvalue (aggregatetype, value,
date_trunc('hour', stoptime), date_trunc('day', stoptime),
date_trunc('month', stoptime), date_trunc('year', stoptime),
split_part(aggregationvalue.value,':',1),
split_part(aggregationvalue.value,':',2),
split_part(aggregationvalue.value,':',3),
split_part(aggregationvalue.value,':',4),
split_part(aggregationvalue.value,':',5));
and messing with my dynamic SQL enough to make sure it hits the index
every time, that I got headed down the wrong track when it didn't work
consistently between applications, different driver versions, and did
work correctly on the old version, which doesn't server prepare
statements.
thanks again, if only for the moral support.
On Thu, 2008-12-18 at 14:52 -0500, Tom Lane wrote:
> Jeremiah Jahn <jeremiah.jahn@gmail.com> writes:
> > This is with jdbc3-415. None of the (jdbc, or pg) change logs since then
> > have mentioned this problem. When run as a prepared statement the first
> > statement will execute and return results, while the next two seem to
> > execute, but return no results.
>
> It seems quite unlikely that there's a generic bug in prepared
> statements that no one has encountered till now. If you've got a real
> bug here I'm sure it's a lot narrower case than that. Can you put
> together a self-contained test case? Also, can you reproduce the
> problem on something newer than 8.1.11?
>
> regards, tom lane
>