3 ms·
We've been working on exactly this for more than a year. Well, not to Amazon PostgreSQL, but to PostgreSQL on our own linux setup. Using ora2pg makes moving the
by irrational 8y ago
We've been working on exactly this for more than a year. Well, not to Amazon PostgreSQL, but to PostgreSQL on our own linux setup. Using ora2pg makes moving the data over fairly simple (once you get all the settings right so the data flows quickly instead of taking forever). The thing that takes a long time is converting and testing all of the thousands of sql statements/triggers/procedures/etc. from Oracle syntax to Postgres syntax. Here is a partial list of things to look for:
replace nvl with coalesce
replace rownum <= 1 with LIMIT 1
replace listagg with string_agg
replace recursive hierarchy (start with/connect by/prior) with recursive
replace oracle insensitive query format with insensitive_query()
replace minus with except
replace SYSDATE with CURRENT_TIMESTAMP
replace trunc(sysdate) with CURRENT_DATE
replace trunc(datelastupdated) with DATE(datelastupduted) or datelastupdated::date
replace artificial date sentinels/fenceposts like to_date(’01 Jan 1900’) with '-infinity'::date
remove dual table references
replace decode with case statements
replace unique with distinct
replace to_number with ::integer
replace mod with % operator
replace merge into with INSERT ... ON CONFLICT… DO UPDATE/NOTHING
change the default of any table using sys_guid() as a default to gen_random_uuid()
oracle pivot and unpivot do not work in postgres - use unnest
ORDER BY NLSSORT(english, 'NLS_SORT=generic_m') becomes ORDER BY gin(insensitive_query(english) gin_trgm_ops)
replace UNISTR( with U&’
Oracle: uses IS NULL to check for empty string; postgres uses empty string and null are different
Fix string IS NULL comparisons: field1 IS NULL becomes COALESCE(field1::text, '') = ''
Fix string IS NOT NULL comparisons: field2 IS NOT NULL becomes (field2 IS NOT NULL AND field2::text != '')
If a varchar/text column has a unique index a check needs to be made to make sure empty strings are changed to nulls before adding or updating the column.
PostgreSQL requires a sub-SELECT surrounded by parentheses, and an alias must be provided for it. - SELECT * FROM ( ) A
Any functions in the order by clause must be moved to the select statement (e.g. order by lower(column_name))
Any sort of numeric/integer/bigint/etc. inside of a IN statement must not be a string (including 'null' - don't bother trying to use null="", it won't work).
Concatenating a NULL with a NOT NULL will result in a NULL.
Pay attention to any left joins. If a column from a left join is used in a where or select clause it might be null.
For sequences, instead of table.nextval use nextval()
Recommendation: change all varchar2 columns to be not null and set the default to be ''. This fixes a lot of the issues with the difference between how oracle and postgres treat empty strings and nulls.
- aidos 8y agoCurious about the below, what happens on Oracle? >>> Pay attention to any left joins. If a column from a left join is used in a where or select clause it might be null.
- irrational 8y agoIt's a side effect of empty strings and nulls being different on Postgres, but not on Oracle.
- aidos 8y agoOh really? You’d get empty strings for every column in the outer joined table?
- iaabtpbtpnn 8y agoI led an Oracle to Postgres migration for my company's primary databases, and this is an excellent list of things to look for! It really can be done as long as you just use Oracle as a DB, and haven't invested too much into the Oracle ERP universe.
- danpalmer 8y agoAre there techniques to write the above in compatible ways? For example: "replace mod with % operator", can a shim "mod" function be implemented on Postgres? Do you think it's reasonable to lint/test your way to compatibility before transition, and then remove transitional code afterwards, or do you think it needs to be a long running fork of the codebase? We did the former for our Python 2-3 migration and it worked really well.
- thibautg 8y agoNice list, very useful! For me the most annoying thing has been the (+) operator for LEFT/RIGHT (OUTER) JOIN, which does not exist in PostgreSQL. Generally speaking, PostgreSQL’s syntax feels more logical. https://docs.oracle.com/database/121/SQLRF/queries006.htm#SQLRF52354 https://docs.oracle.com/database/121/SQLRF/queries006.htm#SQ...