Amazon’s Consumer Business Turned Off Final Oracle Database
We've been working on migrating from Oracle to Postgres for a few years now. We are about 2 weeks from being finished. It is not for the faint of heart, but it is totally worth it. The documentation is much much better, performance is equivalent or better, the sql dialect is saner, etc. Other than moving the data itself (ora2pg was invaluable for this), rewriting the queries is what has taken the most amount of time. Some of our tips on differences between oracle and postgres sql:<p>replace nvl with coalesce<p>replace rownum <= 1 with LIMIT 1<p>replace listagg with string_agg<p>replace recursive hierarchy (start with/connect by/prior) with recursive<p>replace minus with except<p>replace SYSDATE with CURRENT_TIMESTAMP<p>replace trunc(sysdate) with CURRENT_DATE<p>replace trunc(datelastupdated) with DATE(datelastupduted) or datelastupdated::date<p>replace artificial date sentinels/fenceposts like to_date(’01 Jan 1900’) with '-infinity'::date<p>remove dual table references (not needed in postgres)<p>replace decode with case statements<p>replace unique with distinct<p>replace to_number with ::integer<p>replace mod with % operator<p>replace merge into with INSERT ... ON CONFLICT… DO UPDATE/NOTHING<p>change the default of any table using sys_guid() as a default to gen_random_uuid()<p>oracle pivot and unpivot do not work in postgres - use unnest<p>ORDER BY NLSSORT(english, 'NLS_SORT=generic_m') becomes ORDER BY gin(insensitive_query(english) gin_trgm_ops)<p>Oracle: uses IS NULL to check for empty string; in postgres, empty string and null are different<p>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.<p>PostgreSQL requires a sub-SELECT surrounded by parentheses, and an alias must be provided for it. - SELECT * FROM ( ) A<p>any functions in the order by clause must be moved to the select statement (e.g. order by lower(column_name))<p>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.<p>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.<p>For sequences, instead of .nextval use nextval('')