What Changed in the Answers When a MySQL App Moved to PostgreSQL

This case study uses a demo project in BareSQL’s code repository: a made-up invoicing app that has run on MySQL for years and is moving to PostgreSQL. The project holds the app’s MySQL schema, the same schema rewritten for both engines with some data, and eleven queries as the app ran them on MySQL. The outputs below are what BareSQL printed.

The plan sounds simple: rewrite the schema and fix whatever PostgreSQL refuses. The trouble is in the queries PostgreSQL doesn’t refuse. They run and return rows, and some of the rows are different.

baresql compare on the invoicing app’s queries: a search for Bob Stone finds his row only on MySQL, where the name is stored as bob stone, and counting paid invoices gives 1 on PostgreSQL and 2 on MySQL, which also counts an invoice whose status is Paid.

The first step costs nothing, since lint connects to nothing:

baresql lint --engines mysql,postgres schema-mysql.sql queries.sql

It reports 15 problems: 12 errors, 3 warnings, and 6 notes hidden because they depend on the data. The errors in the schema are the expected ones: backticks, AUTO_INCREMENT, DATETIME, ENUM and ENGINE=InnoDB, each with what PostgreSQL wants instead. In the queries it flags DATEDIFF(), LIMIT 2, 2, GROUP_CONCAT() and ON DUPLICATE KEY UPDATE, which PostgreSQL refuses, and 3 warnings for queries both engines run with different answers.

Running Both Engines on the Same Data

With the schema rewritten to run on both, baresql compare runs the queries on MySQL and PostgreSQL side by side:

baresql compare --mysql mysql://dev@localhost:3306/ --postgres postgres://dev@localhost/scratch schema.sql queries.sql

Of 15 statements, 4 answered alike and 11 differ. Among those 11 are the 4 refusals lint found. The other 7 run on both engines without an error, and these are the ones a migration has to decide about.

Text That Compares Differently

MySQL’s default collation ignores letter case and accents, so the app has always found customers and invoices that PostgreSQL won’t:

queries.sql:4  SELECT id, email FROM customers WHERE name = 'Bob Stone'
  0 rows on PostgreSQL, 1 on MySQL
  PostgreSQL: no rows
  MySQL: 2, '[email protected]'
  lint: text-compare-case

queries.sql:5  SELECT id, email FROM customers WHERE name LIKE 'émile%'
  0 rows on PostgreSQL, 1 on MySQL
  PostgreSQL: no rows
  MySQL: 3, '[email protected]'
  lint: like-case

queries.sql:8  SELECT COUNT(*) FROM invoices WHERE status = 'paid'
  PostgreSQL: 1
  MySQL: 2
  lint: text-compare-case

The third one is the expensive kind. One invoice was saved with the status 'Paid', and for years MySQL counted it with the paid ones. On PostgreSQL the paid count drops from 2 to 1, and the revenue report with it. The fix is a decision: make the data consistent, with every status in lower case, or compare with LOWER(status) = 'paid' where case shouldn’t matter. Accent-insensitive search needs more on PostgreSQL than LOWER(), so a search box that relied on it needs its own plan.

NULL, Division and Digits

queries.sql:11  SELECT CONCAT(name, ' (', country, ')') FROM customers ORDER BY id
  PostgreSQL: 3 rows
      'Ann Lee (DE)'
      'bob stone ()'
      'Émile Roux (FR)'
  MySQL: 3 rows
      'Ann Lee (DE)'
      NULL
      'Émile Roux (FR)'
  lint: concat-null

queries.sql:14  SELECT COUNT(*) / COUNT(DISTINCT customer_id) FROM invoices
  PostgreSQL: 1
  MySQL: 1.3333
  lint: integer-division, division-by-zero

On MySQL one NULL argument makes the whole CONCAT NULL, so the customer without a country had no address label at all. PostgreSQL skips the NULL and prints 'bob stone ()'. Neither is what anyone wants, and COALESCE(country, '') makes the choice explicit on both. The average number of invoices per customer was 1.3333 on MySQL and is 1 on PostgreSQL, which divides integers as integers. Splitting an amount three ways gives six decimals on MySQL and sixteen on PostgreSQL, and ROUND(amount / 3, 2) gives the same on both. Customers with no country sort first on MySQL and last on PostgreSQL.

What the Move Looks Like Now

The work is now a list: 4 statements to rewrite and 7 answers to decide on. Compare only sees the data it runs on, so the closer the script’s data is to production, odd 'Paid' rows and all, the more it finds. Each code in the output has its own post with the answer every engine gave, listed on the BareSQL Portable page.