PostgreSQL – Creating Materialized Views from Foreign Tables

foreign-datamaterialized-viewpostgresqlpostgresql-9.5

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?

CREATE MATERIALIZED VIEW mv_table_1 AS
  SELECT *
  FROM public.mv_table_1 --The schema & table from the different DB
WITH DATA;

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

Best Answer

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

Steps:

  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
Related Question