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?
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 viewelement_types
), elseUSER-DEFINED
(in that case, the type is identified inudt_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.