Migration
This domain tests moving Oracle, SQL Server or MySQL workloads onto EDB Postgres AI Database / Advanced Server: assessment, tooling, datatype and semantic mapping, and cutover.
Assess before you convert
Inventory tables, indexes, constraints, sequences, views, synonyms, triggers, packages, PL/SQL, scheduler jobs and external tables. Tooling: EDB Migration Toolkit (MTK) for schema and data, ora2pg, EDB*Loader for bulk loads, oracle_fdw/postgres_fdw for live access during transition, and logical replication for a low-downtime cutover.
Mapping rules
- VARCHAR2 to varchar/text, NUMBER to numeric (NUMBER(p,0) to bigint when the range fits), CLOB to text, BLOB to bytea or large objects.
- Oracle DATE carries a time component; map to timestamp, not date.
- Oracle treats
''as NULL; Postgres stores a real empty string. - Oracle uppercases unquoted identifiers; Postgres lowercases them - rewrite any quoted mixed-case code.
- EDB Postgres Advanced Server compatibility covers PL/SQL, packages, DBMS_* built-ins, NVL/DECODE, DUAL, ROWNUM, synonyms and CONNECT BY hierarchies, but coverage is not total; test every package.
Worked example
Oracle code SELECT NVL(status,'NEW') FROM t WHERE ROWNUM <= 10 returns 'NEW' for rows where status is ''. Reasoning: Oracle stored that '' as NULL, so NVL replaced it. After migration the column holds an empty string and NVL passes it through. Rewrite as COALESCE(NULLIF(status,''),'NEW') and replace ROWNUM with LIMIT 10 or a window function.
Traps that cost marks
Assuming compatibility mode eliminates rewrites; expecting Oracle-style implicit casts such as '1' = 1; ignoring sequence caching and currval drift after bulk loads; skipping post-load validation of row counts, checksums, constraint states and index rebuilds.