Postgres: Oracle_fdw: Import foreign schema does not support if not exists

Question

When we use import foreign schema in oracle_fdw, there is no option for IF NOT EXISTS. Due to which if we re-execute the import foreign schema command to import the newly added tables/view we get the error that relation already exists.

As we are not aware of the table/view which were not added in the previous execution it becomes difficult to specify them in LIMIT/EXCEPT clause

Is there any work around available to achieve the IF NOT EXISTS functionality

Laurenz Albe · Accepted Answer · 2022-04-12 10:55:57Z

1

There is no direct way to do that, but you can first find the foreign tables that already exist in the target schema and list them in the EXCEPT clause of IMPORT FOREIGN SCHEMA.

To find all foreign tables in a schema:

SELECT relname
FROM pg_class
WHERE relkind = 'f'
  AND relnamespace = 'schemaname'::regnamespace;

Then

IMPORT FOREIGN SCHEMA "XYZ"
EXCEPT (/* list from above */)
FROM SERVER oraserver
INTO schemaname;

answered Apr 12, 2022 at 10:55

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres: Oracle_fdw: Import foreign schema does not support if not exists

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related