[SOLVED] How to create MVIEW containing dblink in postgresql

How to create MVIEW containing dblink in postgresql

I extracted MVIEW statements from oracle database and executing those statements in postgresql db.

I get below error:

ERROR: syntax error at or near "@". Syntax of MVIEW statement is : CREATE Materialized view XYS as select ***** from target@dblink.

I am not sure if postgresql mview recognizes or parsed '@' symbol or not. Please help.

Please help.

Solution

SQL is standardized, but every database speaks its own dialect, so you cannot expect non-standard SQL statements to port from Oracle to PostgreSQL.

Rather than using a database link, you would use a foreign table in PostgreSQL, so depending on where the target table is, you would install postgres_fdw or oracle_fdw.

Besides, the CREATE MATERIALIZED VIEW statements differ in PostgreSQL and Oracle. PostgreSQL only has what is called REFRESH COMPLETE ON DEMAND in Oracle, so odds are you will have to redesign a little.