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.

Dunt

You should be able to specify the name of the destination table, but that depends on how you are bringing the data in. – Contemn

I'm not a postgres expert, but I know that in mysql you can specify the database name before the table name, using the dot notation: mydatabase.mytable. Have you looked for something similar in postgresql? – Concentre

@Concentre i know that about mysql but sadly we can't do that in postgres – Dunt

What MySQL calls a "database" is in fact a schema. You could put your foreign table into a different schema in Postgres – Spotlight

@GordonLinoff if i create a different table and import my foreign table in that will it get updated when it changes in its parent DB ? – Dunt

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');

Affection

I think this will help.Thanks! – Dunt

Answer 2

Another option is just to load the table on to a different schema e.g.

CREATE SCHEMA foreign_data;
IMPORT FOREIGN SCHEMA public LIMIT TO (customers) FROM SERVER serv INTO foreign_data;

Weever