Oracle Db to PostgreSQL conversion using ora2pg

Question

I am trying to migrate my Oracle database to PostgreSQL using Ora2pg tool. Exported DDL file successfully, but when I am trying to import the same into PostgreSQL server getting some errors as below.

There is a check constraint in Oracle as IS JSON condition, when I exported from Ora2PG it is generated as

 ALTER TABLE Temp_table ADD CONSTRAINT ensure_json1 CHECK (rpdata IS JSON);

When I try to execute the same in PostgreSQL server, getting "Syntax error at or near JSON".

Answer 1

You don't need this in Postgres.

Postgres has a native JSON datatype that validates JSON automatically. In Oracle you need that check constraint, to "turn" a CLOB into a JSON column (without it, the values aren't validated and certain JSON operations don't work)

Just remove that constraint from your Postgres script (assuming the column is indeed defined as json or ideally jsonb)