Import Foreign Type with postgresql_fdw

user_1773570 0
Viewed 1202

I have a database with a custom type which could not import using the IMPORT FOREIGN SCHEMA public FROM SERVER replica_db1 INTO db1 since CREATE FOREIGN TABLE fails. How do I import custom type? Also, the custom type is referred from the public schema, how do I change it to use schema from local server?

reporting=> IMPORT FOREIGN SCHEMA public FROM SERVER replica_db1 INTO db1;
ERROR:  type "public.custom_type" does not exist
LINE 8:   start public.custom_type OPTIONS (column_name 'start'),
                ^
QUERY:  CREATE FOREIGN TABLE table1 (
  id bigint OPTIONS (column_name 'id') NOT NULL,
  start public.custom_type OPTIONS (column_name 'start'),
  "end" public.custom_type OPTIONS (column_name 'end')
) SERVER replica_db1
OPTIONS (schema_name 'public', table_name 'table1');
CONTEXT:  importing foreign table "table1"
reporting=> IMPORT FOREIGN TYPE public FROM SERVER replica_db1 INTO db1;
ERROR:  syntax error at or near "TYPE"
LINE 1: IMPORT FOREIGN TYPE public FROM SERVER replica_db1 INT...

Thanks

postgres-fdw postgresql
1 Answers

you must create the data type "public.custom_type" in your server before import the schema with the command:

IMPORT FOREIGN SCHEMA public FROM SERVER replica_db1 INTO db1;
user_83083810
Related