1

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.

Sagar Maram
  • 13
  • 2

1 Answers1

0

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263