Comments for MySQL to PostgreSQL Migration Tips

[phinjensen]

Comments for https://www.endpointdev.com/blog/2014/11/mysql-to-postgresql-migration-tips/
By David Christensen

To enter a comment:

1. Log in to GitHub
2. Leave a comment on this issue.

[phinjensen]

original author: Ed Avis
date: 2014-11-27T06:56:44-05:00

As a substitute for 'replace into' you can also use a technique like this - for a table t whose primary key is column a:

-- First we insert a row where none exists.
insert into t (a, b, c)
select av, bv, cv
where not exists (
select 0
from t
where a = av
)

-- Now a row with a=av definitely exists. Update it with the correct values for b and c.
update t
set b = bv, c = cv
where a = av

Here I have used Sybase/MSSQL syntax - perhaps Postgres will be a bit different with the 'select ... where not exists' but the principle is the same. First insert a row if none exists, and then update any row that exists.

The advantage is that this usually doesn't require an explicit transaction. If there was no row, then the first insert succeeds and the update is a no-op. If there was a row, the insert is a no-op and the update sets the values. (Only if there is some test-and-set going on with columns b or c would you need an explicit transaction.)

You can generalize this to 'insert or update' several rows at once from a temporary table, and again anyone observing the database sees a consistent picture, without the need to group the two operations in an explicit transaction.

That said, Postgres's transaction handling is very good and doesn't suffer from the deadlocks which the older technology in earlier Sybase and MSSQL versions is sometimes prone to - so these careful manoeuvres to avoid explicit transaction handling are less necessary.

[phinjensen]

original author: Ed Avis
date: 2014-11-27T06:59:57-05:00

P.S. in the case where the row already exists, I would expect this technique to perform somewhat better than delete+insert because there isn't maintenance work on any index which may exist on (a).

[phinjensen]

original author: Pavel Stěhule
date: 2014-11-29T00:34:38-05:00

Few years ago I publish a translation of MySQL functions to Postgres: http://okbob.blogspot.cz/2009/08/mysql-functions-for-postgresql.html

[phinjensen]

original author: Jon Jensen
date: 2014-12-05T20:11:35-05:00

Great write-up, David! An a big amen to your recommendation that all data migration work be scripted. It's always shortsighted to manually munge data if you're going to have to re-do that labor later on a fresh export.