Why am I getting a cross-reference error with my insert/select statement in single database

I have the following INSERT statement, and it works fine without the WHERE clause, but will not work with it. There is a separate database that I have connected to via a dblink connection, and I have created several views that contain data I will need for the database I’m working in.

I have the following statement:

INSERT INTO my_schema.table2 (id, name, app, version, status)
SELECT view.my_view.id, view.my_view.name, view.my_view.app, view.my_view.version, view.my_view.status
FROM view.my_view
WHERE view.my_view.name NOT IN (SELECT my_schema.table2.name FROM my_schema.table2);

The error received is:

ERROR: cross-database references are not implemented: “my_schema.table2.name”

The view and my_schema schemas are both in the same database. I thought maybe it was a weird thing where the FDW could not refer back to a local table, but even when I created a table in my_schema to house the data temporarily, it didn’t work.

Essentially I’m looking to pull data from multiple tables in the FDW via several views, and then aggregate sub-sections of that data into a couple of different tables. The select statement above is part of a function I’m trying to create that will automatically insert data into the local tables from the FDW when the FDW has additional data not found in the local table. Normally, I know you would want to set up a job in the FDW to push data downstream when it’s changed, but the data in this FDW is semi-volatile and gets dropped and then re-created every hour (and I don’t have access or ability to change that or set up triggers), so I need to come up with a solution locally.

Answer

you do not need to write “my_schema.table2.name” as two “.” inside the name will be interpreted as a database.schema.table.