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?

Nick Barnes · Accepted Answer · 2019-06-08T00:57:40.853

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.

it can be written in the definition/options of the foreign data wrapper e.g.: `CREATE SERVER foreign_example_server FOREIGN DATA WRAPPER foreign_db OPTIONS ( dbname 'testtable', extensions 'postgis');` — leole, Jan 17 '22 at 10:06
see Postgres-documentation: [https://www.postgresql.org/docs/13/postgres-fdw.html#id-1.11.7.42.10](Postgres-Homepage: F.33.1.4. Remote Execution Options) — leole, Jan 17 '22 at 10:09

score 0 · Answer 2 · answered Jun 09 '16 at 20:25

Well, obviously you are going to need any non-standard types defined at both ends. Don't forget the FDW functionality is supposed to support a variety of different database platforms, so there isn't any magic way to import remote operations on a datatype. Actually, given that one end could be running on MS-Windows and the other on ARM-based Linux there's not even a sensible way of doing it just with PostgreSQL.

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

2 Answers2