Hacker News new | past | comments | ask | show | jobs | submit login

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 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.

Are 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.

I 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.

Curious 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.

It's a side effect of empty strings and nulls being different on Postgres, but not on Oracle.

Oh really? You’d get empty strings for every column in the outer joined table?

Nice 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.


Guidelines | FAQ | Support | API | Security | Lists | Bookmarklet | Legal | Apply to YC | Contact