2

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.

Shift 'n Tab
  • 8,808
  • 12
  • 73
  • 117
  • Options' `schema_name` is about the source table. Qualify the foreign table name with a schema prefix, i.e. `CREATE FOREIGN TABLE company.ftbl_employee( ...` – Stefanov.sm Jul 27 '22 at 10:28
  • Tried but It doesn't work unfortunately `schema "company" does not exist`. Btw the `company` does not exist in the current database server. It is on the sandbox database server which is the source of FDW foreign server. :( – Shift 'n Tab Jul 27 '22 at 10:33
  • Well, there is no way to create a table in a schema that does not exist and there is no such thing as a foreign schema. Why don't you create `company` schema on the current database server? Just `create schema company;` – Stefanov.sm Jul 27 '22 at 10:35
  • I want to connect the `ftbl_employee` to a different source db which has a `company` schema. what does the `foreign_table_schema` really means? It is a schema on the current db or the schema on the source db? – Shift 'n Tab Jul 27 '22 at 10:36
  • It seems to be a schema on the current db. However I would not peek into the guts (information_schema) but rather use the excellent documentation. Have a look [here](https://www.postgresql.org/docs/current/postgres-fdw.html). – Stefanov.sm Jul 27 '22 at 10:44
  • And maybe it's easier to use [import foreign schema](https://www.postgresql.org/docs/current/sql-importforeignschema.html), it seems very clear and unambiguous to me. [alter foreign table](https://www.postgresql.org/docs/current/sql-alterforeigntable.html) may also be used. – Stefanov.sm Jul 27 '22 at 10:52
  • It seems like its the current db. Now I think I have a different error why the foreign table is still unable to get the data. Thanks man. – Shift 'n Tab Jul 27 '22 at 10:53

1 Answers1

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;
Jim Jones
  • 18,404
  • 3
  • 35
  • 44
  • Yes I understand, My situation is there is a local db where I created the foreign table and map that to the source db. The source db *has* a schema named `company`. I think I successfully map the right schema now however it appears that the connection won't allow it. – Shift 'n Tab Jul 27 '22 at 13:13
  • @Roel which error message are you getting? – Jim Jones Jul 27 '22 at 13:19
  • 1
    Connection timed out. Probably this is a configuration error I think Stefanov.sm already answered my question. The `foreign_table_schema` column value is the schema in a local db. Thanks for the answer anyway it will be helpful to others. – Shift 'n Tab Jul 27 '22 at 13:20