7 ms·
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
by irrational 7y ago
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:
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 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 (not needed in postgres)
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)
Oracle: uses IS NULL to check for empty string; in postgres, empty string and null are different
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 .nextval use nextval('')
- michannne 7y agoUsing SQL Server exclusively has given me a Never-Oracle mindset. Some of these differences sound insane. and > any functions in the order by clause must be moved to the select statement (e.g. order by lower(column_name)) I'd pull the hair outta my head
- hobs 7y agoBut functions in the where clause are an unsargable mess :(
- alexis_fr 7y agoMy top 1 is this one: > Oracle: uses IS NULL to check for empty string; in postgres, empty string and null are different Any programmer who doesn’t distinguish NULL and "" has not passed the 2-year experience. Being generous. How did this make it to production?
- irrational 7y agoDo you mean why does Oracle treat NULL and empty string as the same thing? Internally Oracle changes empty string to NULL values. Oracle won't let you insert an empty string.
- sidcool 7y agoSuper useful. Thanks.
- encoderer 7y agoSomebody can build a consulting business from this HN comment.
- xz0r 7y agoCheckout compilerworks.com they work on exactly this problem.
- ghostbrainalpha 7y agoThis is the first time I have wished Hacker News showed upvotes. They have got to be over 100 with that right?
- echelon 7y agoYou can model it. - Get upvote snapshots of all top-level comments' authors in this thread. Take repeated measurements at relatively low time resolution. Also compare this to any other comments they may be making (have a model of the type of comment: follow-ups that are deeply nested probably have a lower upvote velocity) - Find archive.org snapshots of OP as well as the authors of all other upvoted comment threads. Find the delta from then to now. Some authors will have recent data. - For a better model, construct historical graphs of all authors using these methods and use it to iteratively adjust. - Make sure you understand the ranking algorithm and that it is reflected in your model. Nontrivial, but could be a fun exercise.
- 7y ago
- thibautg 7y agoThanks I’ve sent this comment to our DBAs. Very useful.
- vkaku 7y agoI wonder: How long before someone adds a patch to PostgreSQL as a 'compat' patch, or extension.
- anttisalmela 7y agoI believe EnterpriseDB sells something like that.
- jabl 7y agoThere's a company called enterprisedb that does just that. While they keep their Oracle compatibility stuff proprietary, they do employ several of the postgresql core developers.
- rasz 7y agoand gets sued by Oracle because APIs :(
- aswanson 7y agoIt's almost like you should set up a Patreon for this comment.
- koolba 7y ago> any functions in the order by clause must be moved to the select statement (e.g. order by lower(column_name)) What does this refer to? AFAIK, you can invoke functions just about anywhere including in the ORDER BY clause: => CREATE FUNCTION some_func (int) RETURNS int AS $$ SELECT $1 + 1; $$ LANGUAGE SQL; CREATE FUNCTION => SELECT t.* FROM (SELECT 1 AS a) t ORDER BY some_func(t.a); a --- 1 (1 row) Are you referring to not being able to reference column aliases in an ORDER BY clause? (work around is to use a subquery) => SELECT 1 AS a ORDER BY some_func(a); ERROR: column "a" does not exist LINE 1: SELECT 1 AS a ORDER BY some_func(a);
- ddgflorida 7y agoVery useful - anyone using oracle should try to use ANSI keywords now to avoid work in the future, always use COALESCE instead of NVL, use ANSI date functions, etc.
- hamilyon2 7y agoHow do you develop and debug triggers?
- irrational 7y agoI'm not sure what you mean. Triggers are developed and debugged in Postgres the same as in Oracle.
- hamilyon2 7y agoI am curious, what tools do you use? No secret, that with oracle you have few full-blown IDEs where you can literally step by step execute triggers, write unit tests for them etc.
- irrational 7y agoOh, well, honestly none. I just write them by hand. I've written hundreds that way. It probably would be faster to investigate tools, but it seems like there is always something with higher priority to work on.
- WorldMaker 7y ago> replace merge into with INSERT ... ON CONFLICT… DO UPDATE/NOTHING PostgreSQL supports MERGE statements [1], and a cursory glance doesn't seem to show that Oracle deviates that much from the SQL standard. Is there a particular reason you are avoiding the merge statement or is it simply a readability preference for the non-standard "upsert" ON CONFLICT extension? (I'm curious because I was a big fan of MERGE statements in a past life. Part of being allowed to use them in that codebase was proving that the standard was pretty well followed across DB vendors [not that we used more than one], as well as that MERGE performance was critical to some very large dataset joins with complex update logic.) [1] https://www.postgresql.org/message-id/attachment/23520/sql-merge.html https://www.postgresql.org/message-id/attachment/23520/sql-m...
- JacKTrocinskI 7y agoPostgreSQL does not support the MERGE statement (at least not yet).
- twic 7y agoIIRC there was a discussion about adding the full ANSI MERGE statement, but in the end they added INSERT ... ON CONFLICT and left it at that: https://www.postgresql.org/docs/12/sql-insert.html#SQL-ON-CONFLICT https://www.postgresql.org/docs/12/sql-insert.html#SQL-ON-CO...
- WorldMaker 7y agoInteresting, I can see where some of my confusion comes from if it was discussed but not completed. It's somewhat disappointing as I do think the ANSI MERGE statement is a good tool.
- snuxoll 7y agoThe MERGE statement is less functional and has a LOT of hidden gotchas, I won’t argue that I can be useful but I don’t disagree when them not providing the footgun.
- JacKTrocinskI 7y agoI have a hard time believing PostgreSQL performance is better than Oracle, can you give an example? How big is your database and how much did migrating cost you?
- stock_toaster 7y agoThink of how much you can spend on beefy hardware though, if you go with PostgreSQL, versus spending that money on Oracle licensing.
- adwf 7y agoYep, it's surprising how much performance you can get when you're not paying Oracle millions for licensing. A lot of people who think Oracle is amazingly fast, think so because who puts a million dollar DB on thousand dollar hardware? Put any major DB on equivalent hardware and you'll get some pretty damn good performance.
- reactor 7y agoThis, wish I could upvote this more.
- olavgg 7y agoA decade ago Sun Microsystems published a benchmark showing that PostgreSQL was really close to Oracle performance. https://www.informationweek.com/open-source-postgresql-trails-oracle-in-benchmark-but-not-by-much-/d/d-id/1057195 https://www.informationweek.com/open-source-postgresql-trail...
- marcosdumay 7y agoNobody has any data they can show you, because Oracle's EULA prohibits both running their software and sharing that data, you can only do one of those. My experience is that Postgres has better performance than Oracle on much smaller machines. If you have very simple data, with very simple joins, and never deviate from the trivial "select columns from table join table using (foreign key)" you may have a different experience, because Oracle is just great on those.
- 7y ago
- thomasjudge 7y agoWhat about pl/sql to pl/pgsql?
- irrational 7y agoThis wasn't too bad. The first one was the worst - figuring out the difference in the syntax. But after that it was fairly simple to convert all the rest to Postgres syntax. It took a few days to convert all of them in our system (about 500).
- Pxtl 7y agoA lot of this sounds similar to MS SQL Server - the MERGE one sticks out, does Postgres not have MERGE?
- zkomp 7y agoNot yet, merge is broken though and postgres has an alternative that actually do handle races.
- cabaalis 7y agoNow do MSSQL.. (Gets pen and notepad)
- sk5t 7y agoI would note that "foo IN (a, b, c)" can often be better done as "foo = ANY(ARRAY[a, b, c])" in Postgres - not least because you can shift to passing in an array of variable size without having to construct a special statement for each. For sorting on the result of a function, consider using a subselect. "SELECT foo FROM (select fun(z) as foo from blaz) tmp order by 1".
- fuzzieozzie 7y agoIf you have a DB migration challenge then take a look at www.compilerworks.com
- colek42 7y agoThe best part about this comment is that any errors or new best practices will automatically be updated with the entire knowledge of the world.
- ilaksh 7y agoWow.. someone could start an Oracle -> Postgres migration consulting business just from your comment. I hope they name it SADL Consulting.
- mulander 7y ago> replace rownum <= 1 with LIMIT 1 rownum is a pseudocolumn numbering entries, sorting the results will give you records in non increment rownum. On such queries rownum <= N will give you the record that was first in the results before sorting. LIMIT will give you the first record after sorting. So rownum != LIMIT and if you really want to implement the same logic you would need to use select row_number() over () as rownum in the ported query, put that into a subquery and filter with where rownum <= N in the outer level. Example: select * from (select row_number() over () as rownum, * from pg_class order by relname) as x where x.rownum < 5; select * from pg_class order by relname LIMIT 5; Will give two very different results. The first one maps to oracle: select * from pg_class where rownum < 5 order by relname; The second one doesn't.
- irrational 7y agoOur Oracle queries are written like: select * from ( select * from pg_class order by relname ) where rownum <= 1 So select * from pg_class order by relname limit 1 works for us.
- ducktape 7y ago@irrational, you have my sympathy. But next time you hear of anybody wanting to torture themselves with such an endeavor you might want to save their soul by asking them to look at CompilerWorks.com. Full disclosure, I work at/for CompilerWorks and will be biased since they pay the bills. But as an ultimate compliment for the kind of work we do, one of our customers brought xz0r's comment about us to our attention.