MySQLとPostgreSQLのSQL文の違い

1. はじめに

MySQLとPostgreSQLはどちらも広く使われる代表的なRDBMSですが、SQLの文法に微妙な違いがあったりします。
今までMySQLを使っていたのがPostgreSQLを使う必要が出てきたり、あるいはその逆だったり、またプロジェクトによってこの2つを使い分ける必要があったりする人向けに簡単にまとめてみます。
※今回はパフォーマンス面の違いなどはスコープ外とします。

2. データ型の違い

まずは、データ型についてです。以下の表にしてみました。

用途・機能	MySQLのデータ型	PostgreSQLのデータ型	備考
整数型	TINYINT, SMALLINT, INT, BIGINT	SMALLINT, INTEGER, BIGINT	MySQL特有のTINYINTはPostgreSQLには存在しない（代替はSMALLINT）
小数・浮動小数	FLOAT, DOUBLE, DECIMAL	REAL, DOUBLE PRECISION, NUMERIC	DECIMAL ≒ NUMERIC（精度指定可）
真偽値（Boolean）	TINYINT(1) または BOOLEAN	BOOLEAN	MySQLのBOOLEANは内部的にTINYINT(1)として扱われる
日付と時刻	DATE, DATETIME, TIMESTAMP, TIME	DATE, TIMESTAMP, TIME, INTERVAL	PostgreSQLにはDATETIMEは存在しない（TIMESTAMPが相当）
文字列（基本）	CHAR(n), VARCHAR(n), TEXT	CHAR(n), VARCHAR(n), TEXT	両者に類似。ただしPostgreSQLのTEXTは制限が少なく柔軟
文字列（大容量）	TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT	TEXT	PostgreSQLのTEXTは最大約1GB（1行あたり）まで対応。MySQLは段階的に制限あり
バイナリデータ	BLOB, LONGBLOB など	BYTEA	PostgreSQLではBYTEAを使用
JSON	JSON	JSON, JSONB	JSONBはPostgreSQL特有の効率的な形式
ENUM（列挙型）	ENUM('A', 'B')	明示的なENUM型はなし（CHECK制約等で代替）	PostgreSQLでは柔軟な型定義やドメイン型で対応可能

1つの例として、MySQLではTINYINTで真偽値を表現していた方は、PostgreSQLではBOOLEANを使うようにして下さい。
MySQL

ALTER TABLE USER ADD COLUMN is_admin TINYINT(1) DEFAULT b'0';

PostgreSQL

ALTER TABLE USER ADD COLUMN is_admin BOOLEAN DEFAULT false;

3. 主キーの自動採番

MySQLではAUTO_INCREMENTで自動採番を表現していましたが、PostgreSQLではSERIALで表現します。
MySQL

CREATE TABLE item (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(20) NOT NULL,
  price INT NOT NULL,
  memo VARCHAR(100)
);

PostgreSQL

CREATE TABLE item (
  id SERIAL PRIMARY KEY,
  name VARCHAR(20) NOT NULL,
  price INT NOT NULL,
  memo VARCHAR(100)
);

MySQLでBIGINTの場合、PostgreSQLではBIGSERIALになります。
また、PostgreSQLでは、最近では下記のような書き方もできるようです。

CREATE TABLE item (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(20) NOT NULL,
  price INT NOT NULL,
  memo VARCHAR(100)
);

4. バッククォート

MySQLでテーブル名やカラム名をわかりやすくするために下記のようにバッククォートで囲んでいた人もいると思いますが、シングルクォートではエラーになります。
MySQL

SELECT `id`, `name` FROM `users`;

PostgreSQLに移行する場合は、バッククォートはサクッと外しましょう。
PostgreSQL

SELECT id, name FROM users;

5. GROUP BYの厳密さの違い

まずは下記のSQLを見てください。

SELECT department_id, age
FROM employees
GROUP BY department_id;

上記のクエリはMySQLだと通るのですが、PostgreSQLではエラーになります。
何故かわかりますか？
PostgreSQLでは、**「GROUP BYに含まれていないカラムは、集約関数でなければならない」**と定められています。

SELECT department_id, MAX(age)
FROM employees
GROUP BY department_id;

これだとMAXが集約関数に該当するので、OKです。

ただ、本来なら1つ目のSQLは問題ある書き方なので、この辺りはPostgreSQLの方がしっかりとした仕用になっているといえるでしょう。
もっとも、MySQLでも5.7以降は初期状態でONLY_FULL_GROUP_BYという設定が有効になっているので、設定を変えていなければエラーになるはずです。
設定はSELECT @@global.sql_mode;で確認することができます。

6. 関数と演算子の違い

関数や演算子についても、いくつか違いがあったりするので、簡単にまとめてみます。

用途	MySQL	PostgreSQL	備考
文字列の連結	CONCAT('A', 'B')	'A' || 'B'	PostgreSQLでは||が標準的連結演算子
現在日時の取得	NOW()	CURRENT_TIMESTAMP または NOW()	両者ともNOW()は使用可能（同等）
NULLの判定	IFNULL(expr1, expr2)	COALESCE(expr1, expr2)	PostgreSQLにはIFNULLがなくCOALESCEが標準
正規表現マッチ	column REGEXP 'pattern'	column ~ 'pattern'	PostgreSQLでは~（マッチ）や~*（大文字小文字無視）など
条件式（IF）	IF(condition, true, false)	CASE WHEN ... THEN ... ELSE ... END	PostgreSQLにIF()はなくCASE式で代用

MySQLではIF()やCONCAT()など、手軽に使える関数が多く用意されています。一方で、PostgreSQLではSQL標準を重視した構文となっており、代わりにCASE文や演算子（||）で記述する場面が増えます。とくに正規表現やNULLの扱いでは差が出やすいため、変換・移行時は注意が必要です。

条件式の例を挙げてみます。
MySQL

SELECT name,
       IF(is_admin, '管理者', '一般ユーザー') AS user_type
FROM users;

PostgreSQL

SELECT name,
       CASE
           WHEN is_admin THEN '管理者'
           ELSE '一般ユーザー'
       END AS user_type
FROM users;

7. まとめ

他にも色々違いはありますが、全てあげたらきりがないので、重要そうなものを中心にまとめてみました。
印象としては、MySQLは柔軟さを重視している一方、PostgreSQLはSQL標準への準拠に重きを置いている感じですね。
2つのRDBMSの違いを理解しつつ、使いこなしていきたいですね。