How to create an (FDW) foreign table and map it to a different schema?

Question

This is how I defined the foreign table:

CREATE FOREIGN TABLE ftbl_employee (
    id UUID,
    name VARCHAR,
)
SERVER company_registry_dbserver
OPTIONS (schema_name 'company', table_name 'company_employee');

It created the foreign table successfully. However, when I list the foreign table, It has defaulted to public schema. See foreign_table_schema column:

> select * from information_schema.foreign_tables;

foreign_table_catalog	foreign_table_schema	foreign_table_name	foreign_server_catalog	foreign_server_name
sandbox	public	ftbl_employee	sandbox	company_registry_dbserver

I would like to map it into the company schema in our sandbox database server instead of the public schema.

Answer 1

The column information_schema.foreign_tables holds the schema where the foreign table is stored locally, not the schema of the table in the target database. So, there is no way you can create this foreign table in the schema company if it does not exist locally! You need to either locally run ..

CREATE SCHEMA company;

.. or live with the foreign table in the public schema. Keep in mind that a foreign table is nothing more than a "gateway" to a table that resides in a different database / server. If you wanna know more details on the foreign table, e.g. name or schema, check the view pg_foreign_table:

SELECT relname, ftoptions 
FROM pg_foreign_table 
JOIN pg_class ON ftrelid = oid;