passing the where clause to the foreign database

[rotten]

I was reasearching this issue for the (Multicorn) Treasure Data FDW: https://github.com/komamitsu/td-fdw/issues/6

The problem I'm having is that I have a treasure data table with several hundred million rows, but I only want to pull back the most recent rows. What happens at this time is that the foreign data wrapper requests the entire table back, and then applies the filter in temp space rather than forwarding my where clause and only pulling back the rows I need.

This works (passes the where clause through):

select 
  *
from
  my_foreign_table
where
  time > 1477958400
limit 100

(Although it doesn't pass the limit through, but I can live with that.)

This does not:

select
  *
from
  my_foreign_table
where
  time > extract(epoch from (date_trunc('month', current_timestamp at time zone 'utc')))
limit 100

Nor this:

select
  *
from
  my_foreign_table
where
  time > (select extract(epoch from (date_trunc('month', current_timestamp at time zone 'utc'))))
limit 100

I'm not sure if this is a problem that can be solved in Multicorn, or if it is something we'll have to push back on PostgreSQL to fix. (Or, if you have a trick to make this work.)

[rdunklau]

Hello. It comes from the fact that the types are mismatched: the rule to compare an int to a double precision is to cast the int to a double, and then perform the comparison.

If you want to be able to send the WHERE clause, the types should match. In your case, I think it would be fine to cast the result of extract to an integer.

[rotten]

That did it. Thanks!