Différences de syntaxe SQL entre PostgreSQL et MySQL

La plupart de la syntaxe PostgreSQL vs MySQL que vous écrivez chaque jour est identique. SELECT, JOIN, WHERE, GROUP BY et les INSERT de base fonctionnent de la même manière dans les deux. Le problème vient des 20 pour cent restants, les recoins spécifiques au dialecte où une requête qui s'exécute proprement sur MySQL génère une erreur de syntaxe sur Postgres (et vice versa). Ce guide cartographie ces recoins avec des exemples côte à côte afin que vous puissiez porter des requêtes sans tâtonnements.

Si vous migrez une base de données, écrivez une requête qui doit fonctionner sur les deux moteurs, ou essayez simplement de comprendre pourquoi un extrait Stack Overflow ne fonctionne pas dans votre console, la réponse est presque toujours l'une des quelques différences connues. UPSERT, booléens, opérateurs d'ensemble, auto-incrémentation et gestion des chaînes causent la grande majorité des douleurs de portage.

Syntaxe PostgreSQL vs MySQL : les 20% qui cassent vraiment#

Les comparaisons business de ces deux bases de données se concentrent sur les performances, les licences et la réplication. Utiles pour choisir une stack, inutiles quand vous avez une requête cassée sous les yeux. Ce qu'il vous faut, c'est le tableau de syntaxe.

Voici la version courte des divergences dialectales. Le reste de ce guide détaille chaque ligne avec des exemples concrets.

Fonctionnalité	PostgreSQL	MySQL
UPSERT	INSERT ... ON CONFLICT ... DO UPDATE	INSERT ... ON DUPLICATE KEY UPDATE
Auto-incrément	SERIAL / GENERATED ... AS IDENTITY	AUTO_INCREMENT
Booléen	BOOLEAN natif (true/false)	Alias TINYINT(1), stocke 0/1
Opérateurs ensemblistes	INTERSECT, EXCEPT supportés	INTERSECT/EXCEPT seulement depuis 8.0.31
Concaténation de chaînes	Opérateur ||	CONCAT() (|| signifie OU)
Sensibilité à la casse	les identifiants sont mis en minuscules	dépend du système d'exploitation et de la config de la table
Guillemets	guillemets doubles pour les identifiants	backticks pour les identifiants
LIMIT/OFFSET	LIMIT n OFFSET m	LIMIT m, n ou LIMIT n OFFSET m

Règle empirique : si une requête échoue après une migration, vérifiez d'abord UPSERT, les comparaisons booléennes et le quoting des identifiants. Ces trois points représentent la majorité des casses.

UPSERT : ON CONFLICT vs ON DUPLICATE KEY UPDATE#

C'est le piège le plus courant lors du portage de requêtes, et il n'existe pas de traduction automatique propre. Les deux moteurs permettent d'insérer une ligne et de la mettre à jour si une clé existe déjà, mais la syntaxe est complètement différente.

MySQL utilise ON DUPLICATE KEY UPDATE, qui se déclenche sur toute collision de clé unique ou primaire :

INSERT INTO users (id, email, login_count)
VALUES (1, 'a@example.com', 1)
ON DUPLICATE KEY UPDATE login_count = login_count + 1;

PostgreSQL utilise ON CONFLICT, et vous oblige à nommer la cible du conflit (la colonne ou la contrainte sur laquelle vous attendez une collision) :

INSERT INTO users (id, email, login_count)
VALUES (1, 'a@example.com', 1)
ON CONFLICT (id) DO UPDATE
SET login_count = users.login_count + 1;

Deux choses posent problème ici. Premièrement, Postgres exige la cible du conflit, tandis que MySQL la déduit de la clé unique violée. Deuxièmement, la façon de référencer la ligne entrante diffère. MySQL expose les nouvelles valeurs via VALUES(col) (ancienne syntaxe) ou un alias de ligne, alors que Postgres les expose via la pseudo-table spéciale EXCLUDED :

-- PostgreSQL référençant la valeur entrante
ON CONFLICT (id) DO UPDATE
SET login_count = EXCLUDED.login_count;

Si vous ne retenez qu'une seule différence de cet article, que ce soit celle-ci. L'UPSERT est l'endroit où la plupart des requêtes échouent silencieusement lors de la traduction.

Auto-incrémentation : SERIAL et IDENTITY vs AUTO_INCREMENT#

Les clés primaires auto-incrémentées existent dans les deux moteurs, mais leur déclaration diffère.

MySQL attache AUTO_INCREMENT à la colonne :

CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  total DECIMAL(10,2)
);

PostgreSQL propose deux approches. La plus ancienne et répandue est SERIAL, un raccourci qui crée une séquence en arrière-plan :

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  total NUMERIC(10,2)
);

L'approche moderne et conforme à la norme SQL (recommandée sur Postgres 10 et versions ultérieures) est GENERATED ... AS IDENTITY :

CREATE TABLE orders (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  total NUMERIC(10,2)
);

Une différence subtile connexe : le DECIMAL de MySQL et le NUMERIC de Postgres sont fonctionnellement identiques (type à précision arbitraire) et acceptent les deux mots-clés. Mais NUMERIC est le nom standard SQL que vous verrez dans la documentation Postgres, donc ne soyez pas surpris lors du portage dans l'autre sens.

Booléens : BOOLEAN natif vs TINYINT(1)#

PostgreSQL dispose d'un vrai type BOOLEAN qui stocke true et false (ainsi que NULL). Vous pouvez le comparer directement :

SELECT * FROM users WHERE is_active = true;
SELECT * FROM users WHERE is_active;  -- également valide dans Postgres

MySQL n'a pas de booléen natif. BOOLEAN et BOOL sont des alias pour TINYINT(1), qui stocke des entiers. true correspond à 1 et false à 0. Cela fonctionne généralement, mais cela pose problème à deux endroits :

• Les comparaisons avec les littéraux true/false fonctionnent car MySQL les traite comme 1/0, mais vos données stockées sont des entiers, donc un SELECT renvoie 1 et 0, pas true et false.
• Toute valeur autre que 0 est considérée comme vraie dans le monde des entiers de MySQL, donc les colonnes TINYINT(1) peuvent techniquement contenir 2, 5 ou -1 si votre application est négligente.

Lorsque vous portez une colonne booléenne de Postgres vers MySQL, attendez-vous à ce que les données apparaissent sous forme de 0/1 et ajustez tout code d'application ou mappage ORM qui attendait les chaînes true/false.

Opérateurs d'ensemble : INTERSECT et EXCEPT#

PostgreSQL prend en charge tous les opérateurs d'ensemble standards : UNION, UNION ALL, INTERSECT et EXCEPT. Ils se comportent tous comme le décrit la norme SQL.

SELECT id FROM table_a
INTERSECT
SELECT id FROM table_b;

MySQL a longtemps été à la traîne. UNION et UNION ALL ont toujours fonctionné, mais INTERSECT et EXCEPT ne sont arrivés qu'avec MySQL 8.0.31. Si vous ciblez une version antérieure de MySQL, vous ne pouvez pas les utiliser du tout et devez réécrire la logique.

La solution de contournement classique pour INTERSECT sous MySQL est une jointure interne ou une sous-requête IN :

-- INTERSECT réécrit pour les anciennes versions de MySQL
SELECT DISTINCT a.id
FROM table_a a
JOIN table_b b ON a.id = b.id;

Et pour EXCEPT, un motif LEFT JOIN ... IS NULL ou NOT IN / NOT EXISTS :

-- EXCEPT réécrit pour les anciennes versions de MySQL
SELECT a.id
FROM table_a a
LEFT JOIN table_b b ON a.id = b.id
WHERE b.id IS NULL;

Attention à la gestion des valeurs NULL. NOT IN avec une sous-requête qui renvoie une valeur NULL peut produire un résultat vide de manière inattendue. NOT EXISTS et le motif LEFT JOIN ... IS NULL sont plus sûrs lorsque des NULL sont possibles.

Chaînes, guillemets et concaténation#

Cette catégorie provoque les erreurs les plus déroutantes car les mêmes caractères signifient des choses différentes.

Guillemets pour les identifiants#

PostgreSQL utilise les guillemets doubles pour les identifiants (noms de tables et de colonnes) et les guillemets simples pour les chaînes littérales :

SELECT "userName" FROM "Users" WHERE name = 'Alice';

MySQL utilise les backticks pour les identifiants par défaut :

SELECT `userName` FROM `Users` WHERE name = 'Alice';

MySQL peut être configuré pour accepter les guillemets doubles comme identifiants en activant le mode ANSI_QUOTES, mais par défaut, des guillemets doubles autour d'une chaîne peuvent se comporter comme un littéral, ce qui entraîne des bugs déroutants lorsque vous copiez du SQL Postgres dans une console MySQL.

Concaténation de chaînes#

PostgreSQL utilise l'opérateur standard || pour concaténer les chaînes :

SELECT first_name || ' ' || last_name AS full_name FROM users;

Dans MySQL, || signifie OU logique par défaut, pas concaténation. Vous devez utiliser la fonction CONCAT() :

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;

C'est une erreur silencieuse qui guette. Une concaténation avec || copiée depuis Postgres ne générera pas d'erreur dans MySQL ; elle sera évaluée comme un OU booléen et retournera 0 ou 1, ce qui est bien plus difficile à déboguer qu'une erreur de syntaxe.

Sensibilité à la casse#

PostgreSQL réduit les identifiants non guillemetés en minuscules, donc MyTable et mytable sont le même objet sauf si vous les guillemetez. La sensibilité à la casse des identifiants dans MySQL dépend du système d'exploitation et du paramètre lower_case_table_names, ce qui signifie qu'une requête peut fonctionner sur le Mac d'un développeur et échouer sur un serveur de production Linux. En cas de doute, adoptez une convention de nommage cohérente en minuscules avec underscores et vous éviterez tout le problème.

LIMIT, OFFSET et Pagination#

Les deux moteurs prennent en charge LIMIT n OFFSET m, la forme portable à privilégier :

SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 20;

MySQL accepte également une syntaxe abrégée avec virgule que PostgreSQL ne comprend pas :

-- MySQL uniquement : sauter 20, prendre 10
SELECT * FROM products ORDER BY id LIMIT 20, 10;

Notez que l'ordre est inversé dans la forme avec virgule (offset d'abord, puis count), ce qui est une source fréquente d'erreurs de décalage de page. Utilisez la syntaxe explicite LIMIT ... OFFSET ... et vos requêtes de pagination seront portables dans les deux sens.

Porter des requêtes sans tâtonner#

Lorsque vous déplacez une requête entre ces dialectes, traitez les fonctionnalités à haut risque dans l'ordre plutôt que de l'exécuter aveuglément et de lire les messages d'erreur. Une checklist pratique :

• Remplacez la syntaxe UPSERT (ON DUPLICATE KEY UPDATE par ON CONFLICT, ou l'inverse) et corrigez la référence de ligne (VALUES() par EXCLUDED).
• Remplacez la concaténation de chaînes || par CONCAT() pour MySQL.
• Convertissez le guillemetage des identifiants (backticks en guillemets doubles, ou l'inverse).
• Vérifiez les colonnes booléennes et attendez-vous à des données 0/1 sur MySQL.
• Remplacez INTERSECT/EXCEPT par des jointures si vous ciblez MySQL avant la version 8.0.31.
• Normalisez les déclarations d'auto-incrémentation (SERIAL/IDENTITY contre AUTO_INCREMENT).

Lire une requête dense pour trouver ces points est bien plus facile une fois qu'elle est formatée. Un bloc de SQL sur une seule ligne cache la clause ON CONFLICT et le || égaré. Notre formateur SQL multi-dialecte reformate et indente les requêtes pour PostgreSQL et MySQL, afin que vous puissiez voir clairement la structure avant de commencer la traduction. Pour un aperçu plus approfondi des différences de formatage entre les moteurs, consultez notre guide sur comment formater une requête SQL pour la lisibilité entre dialectes.

Si votre travail implique également d'envoyer les résultats de requêtes à une API ou une configuration, le même principe de lisibilité s'applique à vos charges utiles. Notre formateur JSON nettoie la couche de données, et le tutoriel sur comment formater du JSON en JavaScript couvre les motifs JSON.stringify qui s'associent à la sortie de base de données.

Le Bilan des Différences de Syntaxe entre PostgreSQL et MySQL#

Les différences de syntaxe entre PostgreSQL et MySQL sont concentrées, pas dispersées. Les SELECT, JOIN et les agrégations standards sont portables ; les problèmes se regroupent autour de l'UPSERT, des booléens, des opérateurs ensemblistes, de la concaténation de chaînes, du guillemetage des identifiants et de l'auto-incrémentation. Maîtrisez ces six points et vous pourrez porter la grande majorité des requêtes réelles sans surprises.

Gardez ce tableau mental : lorsqu'une requête échoue après une migration, la cause est presque certainement l'un des coins dialectaux ci-dessus, pas votre logique principale. Formatez d'abord la requête, repérez les clauses à haut risque, traduisez-les délibérément, et vous transformez un jeu de devinettes frustrant en une correction rapide et mécanique.

Foire aux questions#

Quelle est la plus grande différence syntaxique entre PostgreSQL et MySQL ? L'UPSERT est la différence pratique la plus importante. PostgreSQL utilise INSERT ... ON CONFLICT ... DO UPDATE avec une cible de conflit explicite et la pseudo-table EXCLUDED, tandis que MySQL utilise INSERT ... ON DUPLICATE KEY UPDATE et déduit la clé. Il n'existe pas de traduction automatique propre, c'est donc la clause la plus susceptible de poser problème lors du portage d'une requête.

Pourquoi ma concaténation de chaînes PostgreSQL échoue-t-elle dans MySQL ? PostgreSQL utilise l'opérateur || pour la concaténation de chaînes, mais dans MySQL, || signifie OU logique par défaut. Copier une requête Postgres dans MySQL ne générera pas d'erreur de syntaxe ; elle renverra silencieusement 0 ou 1 au lieu d'une chaîne concaténée. Utilisez la fonction CONCAT() dans MySQL pour obtenir le même résultat.

MySQL a-t-il un vrai type BOOLEAN comme PostgreSQL ? Non. PostgreSQL a un type natif BOOLEAN qui stocke true/false. Dans MySQL, BOOLEAN et BOOL sont des alias pour TINYINT(1), qui stocke des entiers, donc true devient 1 et false devient 0. Lorsque vous portez des colonnes booléennes, attendez-vous à ce que les données apparaissent sous forme de 0/1 et mettez à jour tout code qui attendait les valeurs de chaîne.

Puis-je utiliser INTERSECT et EXCEPT dans MySQL ? Uniquement dans MySQL 8.0.31 et versions ultérieures. PostgreSQL supporte INTERSECT et EXCEPT depuis longtemps. Sur les anciennes versions de MySQL, vous devez les réécrire, en utilisant une jointure interne ou une sous-requête IN pour INTERSECT, et un motif LEFT JOIN ... IS NULL ou NOT EXISTS pour EXCEPT, en faisant attention à la gestion des NULL.

La plupart du SQL est-il portable entre PostgreSQL et MySQL ? Oui. Environ 80 pour cent du SQL quotidien, y compris SELECT, JOIN, WHERE, GROUP BY et INSERT basique, est identique ou suffisamment proche pour fonctionner sur les deux moteurs. Les différences se concentrent sur l'UPSERT, les booléens, les opérateurs d'ensemble, le guillemetage des identifiants, la concaténation de chaînes et l'auto-incrémentation, c'est pourquoi une liste de contrôle ciblée est plus efficace que de lire les messages d'erreur un par un.

Quel est le moyen le plus rapide de repérer les différences de dialecte dans une requête ? Formatez d'abord la requête pour que la structure soit visible, puis recherchez les clauses à haut risque. Un formateur qui supporte les deux dialectes, comme le formateur SQL Molixa, indente et aligne le SQL de sorte que les différences de ON CONFLICT, || et de guillemetage ressortent au lieu de se cacher dans une ligne dense.