MySQLとPostgreSQLのコマンド・クエリ対応一覧

　ここ1年半ほど私がお世話しているWEBサービスの一つに、MySQLとPostgreSQLの二つを同時利用しているハイブリッドなものがあって、定常的にMySQLとPostgreSQLを交互に行ったり来たりしながら保守する仕事をやっている。そんな中で、DB関連のコマンドやクエリを発行する時に、しばしば「MySQLだとわかるんだけど、PostgreSQLだと何だっけ？」と云うシーンに出くわすことがあって、自分用のチートシートとして各DBMSのコマンドとクエリを対応させた一覧を作ってみた次第。

## DB接続／基礎編

　DBに接続する時のコマンドや、DBコンソールでの基礎的なコマンドなど。まぁ、この辺はそらんじているところも多いのだが、何事も基礎は重要なので列挙しておく。

### コマンドライン用コマンド

| 用途 | MySQL     | PostgreSQL |
|:-----|:----------|:-----------|
| DBMSバージョン確認 | `mysql -V` | `psql -V` |
| デーモン起動 | `/etc/init.d/mysqld start`, `service mysqld start` | `pg_ctl start -w`, `/etc/init.d/postgresql start`, `service postgresql start` |
| デーモン起動状況確認 | `/etc/init.d/mysqld status`, `service mysqld status` | `pg_ctl status`, `/etc/init.d/postgresql status`, `service postgresql status`, |
| デーモン停止 | `/etc/init.d/mysqld stop`, `service mysqld stop` | `pg_ctl stop -m fast`, `/etc/init.d/postgresql stop`, `service postgresql stop` |
| 自動起動設定 | `chkconfig mysqld on` | `chkconfig postgresql on` |
| DB接続 | `mysql -u {db_user} -p -P 3306 -h localhost {database_name}` | `psql -U {db_user} -w -P 5432 -h localhost -d {database_name}` |
| DB接続（省略形）[^※1] | `mysql {database_name}` | `psql {database_name}` |
| 設定ファイル（既定） | `~/.my.cnf` | `~/.psqlrc` |
| 任意の設定ファイルで接続 | `mysql --defaults-extra-file={file_path}` | 実行ユーザのホームディレクトリに `.pgpass` を置き上述の省略形で接続 [^※2] |
| 直接SQL実行 | `mysql {database_name} -e '{sql_query}'` | `psql {database_name} -c '{sql_query}'` |
| 外部ファイル実行 | `mysql {database_name} < {file_path}` | `psql {database_name} -f {file_path}` |
| データベース作成 | - | `createdb {database_name}` |
| データベース削除 | - | `dropdb {database_name}` |
| ユーザ作成 | - | `createuser {user_name}` |
| ユーザ作成＋パスワード設定 | - | `createuser -P {user_name}` |
| ユーザ削除 | - | `dropuser {user_name}` |
| テーブル定義確認 | `mysql {database_name} -e 'SHOW CREATE TABLE {table_name}'` | `pg_dump {database_name} -s -t {table_name}` |

[^※1]: 設定ファイルにログイン情報を定義しておくことで「DB接続（省略形）」で接続できるようになる。
[^※2]: `.pgpass` の書式は接続DB毎に `{hostname}:{port}:{database_name}:{db_user}:{password}` の行を追加する（ Permission は 600 か 400 ）。

### DBコンソール用クエリ／コマンド

| 用途 | MySQL     | PostgreSQL |
|:-----|:----------|:-----------|
| ヘルプ表示 | `\h`, `help`, `\?` | `\?` |
| Pagerの切り替え | `pager less -S` ⇔ `nopager` | `\pset pager` |
| 結果表示の整形 | クエリ末尾に `\G` | `\x` の後でクエリ実行 |
| データベース一覧 | `SHOW DATABASES` | `\l` |
| データベースへの接続（変更） | `use {database_name}` | `\c {database_name}` |
| 接続中のデータベースを確認 | `\s`, <br>`SELECT database();` | `SELECT current_database();` |
| 接続中データベースのサイズ確認 | `SELECT table_schema,sum(data_length)/1024/1024 as MB FROM information_schema.tables GROUP BY table_schema ORDER BY sum(data_length+index_length) DESC;` | `SELECT pg_size_pretty(pg_database_size(datname)) FROM pg_database WHERE datname=(SELECT current_database());` |
| テーブル一覧 | `SHOW TABLES;` | `\dt`, `\dt+` |
| テーブル詳細 | `DESC {table_name};`, <br>`SHOW FULL COLUMNS FROM {table_name};` | `\d {table_name}` |
| テーブル定義確認 | `SHOW CREATE TABLE {table_name};` | - |
| インデックス一覧 | `SHOW INDEX FROM {table_name};` | `\di`, `\di+` |
| 外部ファイル実行 | `SOURCE {file_path}`, <br>`\. {file_path}` | `\i {file_path}` |
| プロセス確認 | `SHOW PROCESSLIST;`, <br>`SHOW FULL PROCESSLIST;` | `SELECT * FROM pg_stat_activity;` |
| プロセス停止 | `KILL {process_id};` | `SELECT pg_cancel_backend({process_id});` |
| SQLの実行計画を確認 | `EXPLAIN {sql_query};` | `EXPLAIN ANALYZE {sql_query};` |
| 現在のDATEを取得 | `SELECT curdate();` | `SELECT current_date;` |
| 現在のDATETIMEを取得 | `SELECT now();` | `SELECT now();` |
| DBコンソール終了 | `\q`, `quit`, `exit` | `\q` |


## ユーザ管理関連

　PostgreSQLにおけるユーザは、ロールと称され、ユーザとロールは同義となる。

| 用途 | MySQL     | PostgreSQL |
|:-----|:----------|:-----------|
| ユーザ一覧 | `SELECT host,user FROM mysql.user` | `\du` |
| ユーザ作成 | `CREATE USER {user_name};` | `CREATE ROLE {user_name}`, <br>`CREATE USER {user_name}` |
| ユーザ作成＋パスワード設定 | `CREATE USER {user_name} IDENTIFIED BY '{password}';` | `CREATE ROLE {user_name} WITH LOGIN PASSWORD '{password}';` |
| パスワード変更 | `SET PASSWORD FOR {user_name}@'{host_name}' = password('{password}');` | `ALTER USER {user_name} WITH PASSWORD '{password}';` |
| ユーザ名変更 | `RENAME USER {current_user_name} TO {new_user_name}`; | `ALTER USER {user_name} RENAME TO {new_user_name};` |
| ユーザ削除 | `DROP USER {user_name};` | `DROP ROLE {user_name}`, <br>`DROP USER {user_name}` |
| 権限設定 [^※3] | `GRANT {権限名} ON {レベル} TO {user_name};` | `GRANT {権限名} ON {レベル} TO {user_name];` |
| 権限の確認 | `SHOW GRANTS FOR '{user_name}'@'{host_name}';`, <br>`SHOW GRANTS;`, <br>`SHOW GRANTS FOR current_user();` | `\z` |
| 権限レベル(グローバル) | `GRANT {権限名} ON *.* TO {user_name};` | `GRANT {権限名} ON ALL TABLES IN SCHEMA PUBLIC TO {user_name};` |
| 権限レベル(データベース) | `GRANT {権限名} ON {database_name}.* TO {user_name};` | `GRANT {権限名} ON DATABASE {database_name} TO {user_name};` |
| 権限レベル(テーブル) | `GRANT {権限名} ON {database_name}.{table_name} TO {user_name};` | `GRANT {権限名} ON {table_name}[,...] TO {user_name};` |
| 権限レベル(カラム) | `GRANT {権限名} ({column_name_1}[,...]) ON {database_name}.{table_name} TO {user_name};` | `GRANT {権限名} ({column_name_1}[,...]) ON {table_name} TO {user_name};` |
| 権限削除 | `REVOKE {権限名} ON {レベル} FROM {user_name};` | `REVOKE {権限名} ON {レベル} FROM {user_name};` |
| 全権限削除 | `REVOKE ALL PRIVILEGES, GRANT OPTION FROM {user_name};` | `REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA PUBLIC FROM {user_name};` |
| ユーザ作成（全権限付与＋パスワード設定込み） | `GRANT ALL PRIVILEGES ON *.* TO {user_name} IDENTIFIED BY '{password}' WITH GRANT OPTION;` | `GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA PUBLIC TO {user_name} WITH GRANT OPTION;` [^※4] |

[^※3]: 権限名はカンマ区切りで複数指定可能。テーブルレベルのCRUD権限として `SELECT`,`INSERT`,`UPDATE`,`DELETE` があり、他に `CREATE`,`TRIGGER`,`ALL [PRIVILEGES]`,`USAGE` 等がある（MySQLは権限の種類が多い）。
[^※4]: PostgreSQLではGRANT文で同時にパスワード設定を行うことはできない。

## スキーマ／データベース制御関連

　PostgreSQLにはスキーマという概念があり、スキーマの単位でデータベースやユーザが依存している。まぁ、スキーマはプログラマーレイヤーで云うところの名前空間みたいなものか。テーブルやプロシージャといったDBオブジェクトはスキーマに属して、それぞれ個別にロール（権限）を持つ。
　一方でMySQLにはスキーマという概念はデータベースと同義で、テーブルやプロシージャといったDBオブジェクトはデータベース単位に依存することになる。ユーザはデータベースの上位に位置していて、ロール（権限）はユーザに対して付与される形だ。

| 用途 | MySQL     | PostgreSQL |
|:-----|:----------|:-----------|
| スキーマ作成 | - | `CREATE SCHEMA {schema_name};`, <br>`CREATE SCHEMA {schema_name} AUTHORIZATION {user_name};` |
| スキーマ削除 | - | `DROP SCHEMA {schema_name};`, <br>`DROP SCHEMA {schema_name} CASCADE;` |
| サポート中のストレージエンジン | `SHOW ENGINES\G` | - |
| データベース作成 | `CREATE DATABASE {database_name};` | `CREATE DATABASE {database_name};` |
| データベース削除 | `DROP DATABASE {database_name};` | `DROP DATABASE {database_name};` |
| データベースにコメント追加 | - | `COMMENT ON DATABASE {database_name} IS '{comment}';` |
| データベース名変更 | <code>CREATE DATABASE {new_database_name};<br> RENAME TABLE {database_name}.{table_name} TO {new_database_name}.{table_name}[,...];<br> DROP DATABASE {database_name};</code> [^※5] | `ALTER DATABASE {database_name} RENAME TO {new_database_name};` |

　MySQLではストレージエンジンという概念があり、各テーブル毎にエンジンを指定することができる。ストレージエンジンとはデータの格納および取り扱いの制御方式を司っているコンポーネントで、指定されたエンジンによってSQL内部の処理が大きく異なる。原則的に、トランザクションが利用できるInnoDBエンジンを採用しておけば問題ないが、取り扱うデータの種類別に最適なエンジンを選択することでアプリケーション全体のパフォーマンスを最適化できる可能性がある。

[^※5]: MySQL5.1.23以降では `RENAME DATABSE` が廃止になっているので、新規データベースを作成してから `RENAME TABLE` でテーブルを移動することで代替することになる。

## テーブル制御関連

　総じてPostgreSQLの方がMySQLより多彩なフィールド型を持っていて、データの可用性が高いように思える。また、カラムに格納する値を細かく制御できるようになる検査制約（CHECK制約）を持っているPostgreSQLは、スキーマレベルでMySQLよりもセキュアなテーブルを構築できる利点があるかと。つーか、なぜMySQLはCHECK制約を実装しないのだろうか？

| 用途 | MySQL     | PostgreSQL |
|:-----|:----------|:-----------|
| テーブル作成(例) | <code>CREATE TABLE {table_name} (<br> id int NOT NULL AUTO_INCREMENT, <br> string varchar(200) NOT NULL,<br> number int NOT NULL,<br> created_at timestamp NOT NULL,<br> modified_at timestamp NOT NULL,<br> PRIMARY KEY (id) );</code> | <code>CREATE TABLE {table_name} (<br> id SERIAL,<br> string varchar(200) NOT NULL,<br> number integer DEFAULT 0 NOT NULL,<br> created_at timestamp without time zone DEFAULT now() NOT NULL,<br> modified_at timestamp without time zone NOT NULL,<br> PRIMARY KEY (id) );</code> |
| テーブルにコメント追加 | `ALTER TABLE {table_name} COMMENT='{comment}';` | `COMMENT ON TABLE {table_name} IS '{comment}';` |
| テーブルにカラム追加 | `ALTER TABLE {table_name} ADD {column_name} {field_type} {position}`, `ALTER TABLE {table_name} ADD ({column_name} {field_type}[, {column_name} {field_type}...] {position}` [^※25] | `ALTER TABLE {table_name} ADD {column_name} {field_type}[, ADD ...]` [^※24] |
| カラムにコメント追加 | `{column_name} {field_type} COMMENT '{comment}'`, <br>`ALTER TABLE {table_name} MODIFY {column_name} {field_type} COMMENT '{comment}';` | `COMMENT ON COLUMN {table_name}.{column_name} IS '{comment}';` |
| 連番カラム定義 | `{column_name} {field_type} NOT NULL AUTO_INCREMENT` | `{column_name} SERIAL` |
| UNSIGNED属性 | `{column_name} {field_type} unsigned` | - |
| ZEROFILL属性 | `{column_name} {field_type} zerofill` | 整数型フィールドの値を取得時に `to_char()` で整形する |
| フィールドタイプ(1バイト整数) | `tinyint` | - |
| 〃(2バイト整数) | `smallint` | `smallint`, `int2` |
| 〃(3バイト整数) | `mediumint` | - |
| 〃(4バイト整数) | `integer`, `int` | `integer`, `int`, `int4` |
| 〃(8バイト整数) | `bigint` | `bigint`, `int8` |
| 〃(固定小数点) [^※6] | `decimal(precision, scale)`, `numeric(precision, scale)` | `decimal(precision, scale)`, `numeric(precision, scale)` |
| 〃(4バイト浮動小数点) | `float(precision, scale)`, `float(0～23)` | `real`, `float(1～24)`, `float4` |
| 〃(8バイト浮動小数点) | `double(precision, scale)`, `real(precision, scale)`, `double(24～53)` | `double precision`, `float`, `float(25～53)`, `float8` |
| 〃(通貨型) | - | `money` |
| 〃(連番) | - | `smallserial`, `serial`, `bigserial` [^※7] |
| 〃(日付) | `date` | `date` |
| 〃(時間) | `time` | `time`, `time with time zone` |
| 〃(日時) | `datetime` | - |
| 〃(タイムスタンプ) | `timestamp` | `timestamp`, `timestamp with time zone` [^※8] |
| 〃(期間) | - | `interval` |
| 〃(年) | `year` | - |
| 〃(固定長文字列) [^※9] | `char(n)` | `char(n)`, `character(n)` |
| 〃(可変長文字列) [^※9] | `varchar(n)` | `varchar(n)`, `character varying(n)` |
| 〃(無制限テキスト) [^※10] | `tinytext`, `text`, `mediumtext`, `longtext` | `text` |
| 〃(64バイトまでの文字列) [^※11] | - | `name` |
| 〃(シングルバイト文字列) [^※11] | - | `"char"` |
| 〃(バイナリ文字列) | `binary`, `varbinary` | - |
| 〃(バイナリデータ) | `tinyblob`, `blob`, `mediumblob`, `longblob` | `bytea` |
| 〃(真偽値) [^※12] | `boolean`, `bool` | `boolean`, `bool` |
| 〃(列挙型) | `enum( '{value_1}', '{value_2}'[,...] )`, <br>`set( '{option_1}', '{option_2}'[,...] )` | <code>CREATE TYPE {list_name} AS ENUM ( '{value_1}', '{value_2}'[,...] );<br> CREATE TABLE {table_name} ( {column_name} {list_name} );</code> |
| 〃(幾何データ型) | - | `point`, `line`, `lseg`, `box`, `path`, `polygon`, `circle` |
| 〃(ネットワークアドレス) | - | `inet`, `cidr`, `macaddr` |
| 〃(ビット列データ) | `bit(n)`, `bit varying(n)` | `bit(n)`, `bit varying(n)` |
| 〃(配列型) | - | `{field_type}[]`, `{field_type} ARRAY`, `{field_type} ARRAY[n]` [^※13] |
| 〃(JSON型) [^※14] | `json` | `json`, `jsonb` |
| 制約 [^※15] | `NOT NULL`, `UNIQUE`, `PRIMARY KEY`, `FOREIGN KEY`, `REFERENCES`　| `CHECK`, `NOT NULL`, `UNIQUE`, `PRIMARY KEY`, `REFERENCES`, `EXCLUDE` |
| テキスト全文検索 | `CREATE FULLTEXT INDEX {index_name} ON {table_name}( {column_name}  );` [^※21] | `CREATE INDEX {index_name} ON {table_name} USING gin( to_tsvector( {text_search_config}, {column_name} ) );` |
| パーティション作成 | `CREATE TABLE {table_name} ( {column_name} {field_type} ) PARTITION BY {partition_type}({column_name}) [...];` | `CREATE TABLE {partition_name} () INHERITS ({table_name});` |
| パーティションのパージ | `ALTER TABLE {table_name} DROP PARTITION {partition_name}`; | `DROP TABLE {partition_name};`, <br>`ALTER TABLE {partition_name} NO INHERITS {table_name};` |
| テーブルの継承 | - | `CREATE TABLE {child_table_name} ( {column_name} {field_type}[,...] ) INHERITS ({parent_table_name});`, <br>`ALTER TABLE {child_table_name} INHERIT {parent_table_name};` |

　文字列データのスキーマとしては、通例的に200文字以内の文字列データであればvarchar型を利用するのが良いとか云われている。
　またPostgreSQL（8.3以降）では、文字列サイズによってヘッダサイズが調節されるという特性があり、その境界サイズが126バイトとなっている。この境界サイズを意識して文字列型データを取り扱うようにすると、パフォーマンスが向上する。

[^※6]: decimal型とnumeric型は指定可能な最大桁数が異なる。MySQLでは全桁数65、小数点以下桁数30が最大で、PostgreSQLでは小数点前までが131,072桁、小数点以降が16,383桁が最大値であるが、明示的に型定義できる最大精度(precision)は1000が最大値となる。
[^※7]: serial型の採番範囲は、smallserial型が1～32,767、serial型が1～2,147,483,647、bigserial型が1～9,223,372,036,854,775,807となっている。
[^※8]: PostgreSQLのtimestamp with time zone型は `timestamptz` と省略することが可能。
[^※9]: char型やvarchar型の文字数制限が異なる。MySQLではchar型の最大文字数は255と明確化されているが、PostgreSQLでは明確な制限がなく1000万文字ほどでも格納可能。一般的なデータアクセスツール上でのUIがテキストフィールド(<input type="text" />)となることが多く、その場合、改行を含まない一行テキストのデータが格納されることになるが、実際には改行を含んだ複数行の文字列を格納することも可能である。
[^※10]: text型の文字数制限は、MySQLではtext型が65,535、mediumtext型が1,677,725、longtext型が4,294,967,295と制限されている一方、PostgreSQLのtext型では無制限となっている。一般的なデータアクセスツール上でのUIがテキストエリア(<textarea></textarea>)となることが多い。
[^※11]: PostgreSQLのname型と"char"型（ダブルクォーテーションを含んで型名となる）は内部データ型と呼ばれ、それぞれ格納できるデータが最大で64バイトまで、1バイトのみという制限がある。
[^※12]: MySQLのboolean型、bool型はtinyint(1)型のシノニムのため整数型と同等です。PostgreSQLのboolean型、bool型は真偽理論値とNULLを許容します。真偽理論値として有効なリテラル値は「真」状態が `TRUE` `t` `true` `y` `yes` `on` `1` で、「偽」状態が `FALSE` `f` `false` `n` `no` `off` `0` となる。
[^※13]: 配列データ型は一次元から多次元がサポートされている。CREATE TABLE文の実例としては `CREATE TABLE sal_emp ( name text, pay_by_quarter integer[], schedule text[][] );` のようになる。
[^※14]: JSON型はMySQLではバージョン5.7から、PostgreSQLではバージョン9.2から実装されている。PostgreSQLのJSON型は2種類あって、json型は取り扱い時のパフォーマンスが悪いもののJSONオブジェクトのキーの順番や重複したキーのデータまでをも保持できる。一方でjsonb型は参照のパフォーマンスが高速でインデックスが利用可能だが、オブジェクトキーの順番を保持せず、重複キーのデータは最後のもののみとなる。ほとんどのアプリケーションにはjsonb型が向いているといえる。
[^※15]: MySQLでCHECK制約をCREATE TABLE文に含めた場合でもエラーにはならないが、定義しても実際には無視される。MySQLではトリガで代替することも可能。
[^※21]: MySQLのFULLTEXTインデックスは、ストレージエンジンがMyISAMかバージョン5.6.4以降のInnoDBで利用可能。
[^※24]: PostgreSQLでは追加するカラムの位置を指定することができない。
[^※25]: MySQLでは追加するカラムの位置を指定できる。クエリの{position}にて、先頭に追加する場合は`FIRST`、特定のカラムの後に追加する場合は`AFTER {column_name}`で指定できる。

## バックアップ／リストア／クリーンアップ

　原則的にコマンドライン上での操作となるが、テーブル単位のクリーンアップについては、DBコンソールから実行することになる。

| 用途 | MySQL     | PostgreSQL |
|:-----|:----------|:-----------|
| バックアップ(データベース単位) | `mysqldump -B {database_name} > {output_file}` | `pg_dump {database_name} > {output_file}`, <br>`pg_dump -Fc {database_name} > {output_file}` [^※16] |
| バックアップ(全データベース) | `mysqldump -A > {output_file}` | `pg_dumpall > {output_file}` |
| バックアップ(テーブル単位) | `mysqldump {database_name} {table_name} > {output_file}` | `pg_dump > {output_file}` |
| バックアップ(定義のみダンプ) | `mysqldump -A -d > {output_file}` | `pg_dump -s {database_name} > {output_file}` |
| リストア(データベース単位) | `mysql {database_name} < {backup_file}` | `psql -f {backup_file} {database_name}` |
| リストア(全データベース) | `mysql < {backup_file}` | `psql -f {backup_file} postgres` |
| リストア(アーカイブ形式の場合) | - | `pg_restore -C -d postgres -Fc {backup_file}` [^※16] |
| クリーンアップ(データベース単位) | - | `vacuumdb {database_name}` |
| クリーンアップ(全データベース) | - | `vacuumdb -a`, <br>`VACUUM ` |
| クリーンアップ(テーブル単位) | `OPTIMIZE TABLE {table_name};` | `VACUUM (VERBOSE, ANALYZE) {table_name};` |
| インデックス再構築 [^※17] | `ALTER TABLE {table_name} ENGINE = {current_storage_engine};`, <br>`REPAIR TABLE {table_name};` | `REINDEX {index_name or table_name or database_name};` |

[^※16]: `pg_dump` のアーカイブ形式でのバックアップは `-F` オプションを付ける。形式は `-c` (カスタム形式)と `-t` (tar形式)で指定できる。
[^※17]: MySQLでデータベース単位でインデックス再構築を行う場合、全テーブルをダンプしてリロードするというハイコストな手続きが必要。ALTER TABLEでのストレージエンジンのNULL変更でテーブル単位でのインデックス再構築が可能。REPAIR TABLEはInnoDB以外のストレージエンジンでしかできない。一方、PostgreSQLではREINDEXでデータベース、テーブル、単一インデックス単位で再構築ができる。

## SQLクエリ編

　基本的なCRUDのクエリについてはMySQLとPostgreSQLでほぼ差がない。トランザクション関連では、処理の途中にセーブポイントが定義できるPostgreSQLの方が柔軟性に富むか。

| 用途 | MySQL     | PostgreSQL |
|:-----|:----------|:-----------|
| INSERT文 | `INSERT INTO [table_name} ( {column_name}[,...] ) VALUES ( {value}[,...] )[,...];` | `INSERT INTO {table_name} ( {column_name}[,...] ) VALUES ( {value}[,...] )[,...];` |
| SELECT文 | `SELECT {item} FROM {table_name} WHERE {condition};` | `SELECT {item} FROM {table_name} WHERE {condition};` |
| UPDATE文 | `UPDATE {table_name}[,...] SET {column_name}={value}[,...] WHERE {condition};` | `UPDATE {table_name} SET {column_name} = {value}[,...] WHERE {condition};`, <br>`UPDATE {table_name} SET ( {column_name}[,...] ) = ( {value}[,...] ) WHERE {condition};` |
| DELETE文 | `DELETE FROM {table_name} WHERE {condition};` | `DELETE FROM {table_name} WHERE {condition];` |
| INSERT後に自動採番の値を取得 | `SELECT LAST_INSERT_ID();` | `INSERT INTO {table_name} ( {seq_column}[,...] ) VALUES ( DEFAULT[,...] ) RETURNING {seq_column};` |
| テーブル結合(内部) | `INNER JOIN`, `CROSS JOIN`, `JOIN` | `INNER JOIN`, `CROSS JOIN`, `JOIN` |
| 〃(外部) | `LEFT JOIN`, `RIGHT JOIN`, `OUTER JOIN`, `NATURAL JOIN` | `LEFT JOIN`, `RIGHT JOIN`, `FULL JOIN` |
| 〃(その他) | `STRAIGHT_JOIN`, `UNION` | `UNION`, `INTERSECT`, `EXCEPT` |
| 副問合せ | サブクエリにて | `WITH`句またはサブクエリにて |
| トランザクション(開始) | `BEGIN`, `START TRANSACTION` [^※18] | `BEGIN`, `START TRANSACTION` |
| 〃(コミット) | `COMMIT` | `COMMIT`, `END` |
| 〃(ロールバック) | `ROLLBACK` | `ABORT`, `ROLLBACK`, `ROLLBACK TO SAVEPOINT` |
| 〃(その他) | `SET autocommit = {bit_value}` | `SAVEPOINT`, `SET TRANSACTION` |
| REPLACE文 | `REPLACE INTO {table_name} ( {column_name}[,...] ) VALUES ( {value}[,...] )[,...];` | - |
| テーブル初期化 [^※19] | `TRUNCATE TABLE {table_name};` | `TRUNCATE TABLE {table_name}[,...] RESTART IDENTITY;` |
| 文字列結合 | `CONCAT('{str_1}', '{str_2}')`, <br>`CONCAT_WS('{separator}', 'str_1', 'str_2')`, <br>`GROUP_CONCAT( {expr} )` | <code>'{str_1}' &#124;&#124; '{str_2}'</code>, <br>`concat( '{str_1}', '{str_2}' )`, <br>`concat_ws( '{separator}', '{str_1}', '{str_2}' )` |
| 文字列エスケープ [^※20] | `'O''Reilly'`, `'O\'Reilly'`, "O'Reilly" | `'O''Reilly'`, `E'O\'Reilly'` |
| 便利な関数 | - | `SELECT format('INSERT INTO %I VALUES(%L)', 'Foo bar', E'O\'Reilly');` |


[^※18]: MySQLでは BEGIN ... END の構文をストアドプログラムの開始と終了として取り扱うため、もしそのブロック内でトランザクションを使用する場合、トランザクションの開始は START TRANSACTION を使用する必要がある。
[^※19]: MySQLではTRUNCATEを実行するユーザがDROP権限を持っている必要があり、PostgreSQLではテーブルにTRUNCATE権限が必要。ともにTRUNCATE時にON DELETEトリガは発行されないが、PostgreSQLではON TRUNCATEトリガを発行する。またPostgreSQLでは初期化するテーブルを複数指定可能。
[^※20]: 文字列リテラルはMySQLでは単一引用符（シングルクオート）と二重引用符（ダブルクオート）が使えるが、PostgreSQLでは単一引用符（シングルクオート）のみとなる。

## その他

　クエリ結果をCSV等のファイルに出力する等、実運用で"あるある"的なもの。

| 用途 | MySQL     | PostgreSQL |
|:-----|:----------|:-----------|
| クエリ結果のCSV出力(コマンドライン) | - | `psql {database_name} -c "{sql_query}" -A -F, > {csv_filename}` |
| クエリ結果のTSV出力(コマンドライン) | - | `psql {database_name} -c "{sql_query}" -A -F $'\t', > {tsv_filename}` |
| クエリ結果のCSV出力(DBコンソール) | `SELECT {item} FROM {table_name} INTO OUTFILE '{csv_filepath}' FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '"' LINES TERMINATED BY '\r\n';` | `COPY ( {sql_query} ) TO '{csv_filename}' WITH CSV DELIMITER ',' FORCE QUOTE * NULL AS '' HEADER;` [^※22] |
| クエリ結果のTSV出力(DBコンソール) | `SELECT {item} FROM {table_name} INTO OUTFILE '{tsv_filepath}' FIELDS TERMINATED BY '\t' ENCLOSED BY '"' ESCAPED BY '"' LINES TERMINATED BY '\r\n';` | `COPY ( {sql_query} ) TO '{tsv_filename}' WITH CSV DELIMITER E'\t' FORCE QUOTE * NULL AS '' HEADER;` [^※22] |
| 出力CSVをテーブルへインポート | `LOAD DATA LOCAL INFILE '{import_filepath}' INTO TABLE {table_name} FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';` [^※23] | `COPY {table_name} FROM '{import_filepath}' WITH CSV DELIMITER ',' FORCE QUOTE * NULL AS '';` |

[^※22]: COPYコマンドのテーブル名の後にSQL文がなければテーブル全体をCSVで出力できる。 `( {column_name}[,...] )` と指定すれば特定カラムのみをCSV化できる。
[^※23]: もしインポートするファイルの1行目にヘッダ行等が含まれている場合、LOAD DATAのオプションに `IGNORE 1 LINES` を指定すれば無視してくれる。

<!-- /*
　追記： 実運用で使えるクエリ集（別記事へ）

* [検索結果をカンマ区切りで出力]()
* [カンマ区切りの文字列を配列化してIN句で照会]()

*/ -->

<br><br>

　とりあえず、こんなところか。運用のケーススタディが増えたら都度追加していこうかと。