Quoting differences between MySQL and PostgreSQL, and converting between them

submited by

Style Pass

2024-07-10 02:00:04

We're hard at work building Doltgres, a Postgres-compatible database with git-inspired version control features. Doltgres is built on top of Dolt, which uses MySQL's syntax and wire protocol. Dolt has tens of thousands of tests of its SQL engine, tests we would love to re-use to make sure Doltgres works the same way. So we're in the process of porting those tests to Doltgres.

One of the first challenges we encountered in this process is that many of our tests use MySQL's rules for quoting identifiers and string literals, and Postgres's rules are quite different.

There are two things you might want to quote in your SQL query: identifiers, like table and column names; and string literals. For example, here's a MySQL query that quotes things:

In other words: MySQL lets you input string literals with either single or double quotes, and uses backticks for quoting identifiers. Postgres is stricter: you must use only single quotes for string literals and double quotes for identifiers, and backticks have no special meaning.

Dolt's SQL engine tests use both single and double quotes interchangeably, and there are tens of thousands of them. We want all those tests to run on Doltgres, but we don't want to rewrite them all, or maintain two different verions for the two databases.