Can the foreign data wrapper fdw_postgres handle the GEOMETRY data type of PostGIS?

Question

I am accessing data from a different DB via fdw_postgres. It works well:

CREATE FOREIGN TABLE fdw_table 
(
  name TEXT,
  area double precision,
  use TEXT,
  geom GEOMETRY
)
SERVER foreign_db 
OPTIONS (schema_name 'schema_A', table_name 'table_B')

However, when I query for the data_type of the fdw_table I get the following result: name text area double precision use text geom USER-DEFINED

Can fdw_postgres not handle the GEOMETRY data type of PostGIS? What does USER-DEFINED mean in this context?

Answer 1

From the documentation on the data_type column:

Data type of the column, if it is a built-in type, or ARRAY if it is some array (in that case, see the view element_types), else USER-DEFINED (in that case, the type is identified in udt_name and associated columns).

So this is not specific to FDWs; you'd see the same definition for a physical table.

postgres_fdw can handle custom datatypes just fine, but there is currently one caveat: if you query the foreign table with a WHERE condition involving a user-defined type, it will not push this condition to the foreign server.

In other words, if your WHERE clause only references built-in types, e.g.:

SELECT *
FROM fdw_table
WHERE name = $1

... then the WHERE clause will be sent to the foreign server, and only the matching rows will be retrieved. But when a user-defined type is involved, e.g.:

SELECT *
FROM fdw_table
WHERE geom = $1

... then the entire table is retrieved from the foreign server, and the filtering is performed locally.

Postgres 9.6 will resolve this, by allowing you to attach a list of extensions to your foreign server object.