Data migrations always have a wide range of challenges. I recently took on a request to determine the difficulty of converting an ecommerce shop's MySQL 5.0 database to PostgreSQL 9.3, with the first (presumably "easier") step being just getting the schema converted and data imported before tackling the more challenging aspect of doing a full assessment of the site's query base to re-write the large number of custom queries that leverage MySQL-specific language elements into their PostgreSQL counterparts.
During the course of this first part, which had contained a number of difficulties I had anticipated, I hit one that I definitely had not anticipated:
ERROR: value too long for type character varying(20)
Surely, the error message is absolutely clear, but how could this possibly be? The obvious answer--that the varchar definitions were different lengths between MySQL and PostgreSQL--was sadly quite wrong (which you knew, …
[Read more]