Can a foreign table have same name as a local table?

Question

My problem is that I am using PostgreSQL database and want to import a table named vfm from other database, say B. But there's a table with the same name table in my current database (A). I'm getting an error in my query saying the relation exists already.

I want to know if there is a way to import foreign schemas into a table with some other name or (preferably) if we can query over the the other database directly without the importing foreign schema into PostgreSQL?

I've not been able to figure out a perfect solution for this.

Answer 1

I assume you are using postgres_fdw; for other foreign data wrappers things are slightly different.

It is easy to define foreign tables without using IMPORT FOREIGN SCHEMA.

If the schema name is myschema, table name is vfm and the foreign server is serv, you could

CREATE FOREIGN TABLE myschema.vfm_2(col1, type1, ...)
   SERVER serv
   OPTIONS (schema_name 'myschema', table_name 'vfm');