2

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

3

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
  • 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 '16 at 11:06
  • OK, it works now. A good tutorial: vertabelo.com/blog/technical-articles/… – andschar Jun 9 '16 at 15:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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