I'd like to run a PostgreSQL query in Python using psycopg2, which filters by a column of type timestamp without timezone. I have a long list of allowed values for the timestamp (rather than a range) and psycopg2 conveniently handles arrays, so I thought that this should work:
SELECT somestuff
FROM mytable
WHERE thetimestamp = ANY (%(times)s)
The times parameter is a list of datetime objects. I've also tried psycopg2.Timestamp(). They both translates to WHERE thetimestamp = ANY (ARRAY['2009-07-06T00:00:00', '2009-07-07T00:00:00', ...]) and unfortunately that fails with the following error:
operator does not exist: timestamp without time zone = text
LINE 3: WHERE thetimestamp = ANY (ARRAY['2009-07-06T00:00:00', '2009-07-07T00:00:00', ...]
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
I've confirmed this in pgAdmin as well, so it's not just psycopg2. What seems to be happening is that Postgres will not implicitly convert an array of strings into an array of timestamps. It will convert a single string fine and the array works fine if I explicitly add ::timestamp to each element in pgAdmin, but I don't know how to do that in psycopg2.
What's the best way of doing this, other than forgetting DB-API parameters and just building the long string of timestamps manually? Is there any way I can get it to cast to the correct type?
Try it like this:
SELECT somestuff
FROM mytable
WHERE thetimestamp = ANY (%(times)s::timestamp[])