The following bug has been logged on the website:
Bug reference: 16597
Logged by: Filip Procházka
Email address: filip@prochazka.su
PostgreSQL version: 11.9
Operating system: Alpine Linux v3.12 (in Docker)
Description:
I've created a server and user
CREATE SERVER test FOREIGN DATA WRAPPER postgres_fdw ...;
CREATE USER MAPPING FOR user SERVER test ...;
and some foreign tables and I'm able to connect and query the foreign db. So
far so good.
The problem is, that the remote server uses enums. I've read the emails
where people are discussing that they don't want the IMPORT FOREIGN SCHEMA
to import also custom types like enums. This is a problem for me, but I
figured that simply declaring the tables with the enum columns as text
instead of the original enum types also works and allows me to query the
tables.
CREATE FOREIGN TABLE cz_sales (
month date,
type_of_customer text,
sold_units_total bigint,
sold_price_total double precision
) SERVER pickle_jar OPTIONS (SCHEMA_NAME 'cz', TABLE_NAME 'sales', updatable
'false');
Now what I didn't expect is that when you add a WHERE or try to compare the
enum columns, the query is sent as is to the remote server and executed
there (I'm guessing here), which means it ignores the types I've defined
locally.
SELECT * FROM cz_sales WHERE type_of_customer IN ('DOCTOR');
[42883] ERROR: operator does not exist: cz.enum_type_of_customer = text
Hint: No operator matches the given name and argument type(s). You might
need to add explicit type casts.
Where: remote SQL command: EXPLAIN SELECT * FROM cz.sales WHERE
((type_of_customer = 'DOCTOR'::text))
But if I add an explicit cast, it works
SELECT * FROM cz_sales WHERE CAST(type_of_customer AS TEXT) IN ('DOCTOR');
But this is very ugly because I've already declared the column as TEXT in
the schema and now I'm casting a TEXT to TEXT. I would expect that the
postgres_fdw adds the type casts to the queries for me.
Thank you for considering this bug/feature
P.S.: this is my first time reporting something to Postgres, sorry if this
is the wrong mailing list for such report.
