I'd like to create a simple materialized view from a table which lies in a different database. The two databases are on the same server.

What do I have to add to make the query access the foreign database and the table there?

  FROM public.mv_table_1 --The schema & table from the different DB

I tried using the fully qualified table name (db name before the schema name) but this results in an error:

References to other dbs are not implemented


1 Answer 1


PostgreSQL databases cannot "see" each other by default. This is a good thing.

You can make one pg database see another by using the postgres_fdw Foreign Data Wrapper: https://www.postgresql.org/docs/current/static/postgres-fdw.html


  1. Add the extension
  2. Create the foreign server
  3. Create the user mapping
  4. Create the foreign table. See https://www.postgresql.org/docs/current/static/sql-importforeignschema.html if you have 9.5+
  5. Create the materialized view pointing to the foreign table
  • THank's for the explanation. However when executing the IMPORT FOREIGN SCHEMA command I always get a syntax error although I'm pretty sure that my syntax's alright: IMPORT FOREIGN SCHEMA public LIMIT TO (table_1) FROM SERVER foreign_server INTO public; Table_1 is in the public schema of DB2 and I want it to be read into the public schema of DB1. Any idea?
    – andschar
    Jun 9, 2016 at 11:06
  • OK, it works now. A good tutorial: vertabelo.com/blog/technical-articles/…
    – andschar
    Jun 9, 2016 at 15:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.