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