MySQLとPostgreSQLのコマンドの違いまとめ

# はじめに
普段はMySQLを使用することが多いのですが、PostgreSQLを使用する際にコマンドの違いに戸惑いましたので、備忘録としてコマンドの違いをまとめました。

# 前提
ローカルにはインストールしたくなかったのでDockerでPostgreSQLコンテナを立ち上げて使用しました。
チームメンバーはローカルにインストールしていたので、コマンドを合わせるためにDocker-compose.ymlは以下のようにしました。

<details>
<summary>Docker-compose.yml</summary>

```yml
version: '3'

services:
  db:
    image: postgres:14
    container_name: postgres_pta
    ports:
      - 5432:5432
    volumes:
      - db-store:/var/lib/postgresql/data
    environment:
      POSTGRES_USER: ${USER}  # システムのユーザー名を使用
      POSTGRES_HOST_AUTH_METHOD: trust  # パスワード認証を不要に
      POSTGRES_DB: postgres   # デフォルトのデータベース名
volumes:
  db-store:
```
:::note alert
DBにアクセスする際にパスワードが不要な設定にしています。
開発環境でのみ使用するようにしてください。
:::
</details>

<details><summary>DBコンテナに入る方法</summary>

今回の設定では以下のコマンドでDB内に入れます。
```bash
docker compose exec db psql -U $(whoami) -d postgres
```
- docker exec: 実行中のDockerコンテナ内でコマンドを実行するためのコマンド
- db: docker-compose.ymlで定義したサービス名 
- postgres_pta: docker-compose.ymlで指定したコンテナ名
- psql: PostgreSQLのコマンドラインクライアント
- -U $(whoami): ホストマシンの現在のユーザー名を指定
- -d postgres: データベース名を指定する。ここでは「postgres」というデータベース

:::note warn
-d オプションでデータベースを指定しないとデフォルトで接続ユーザーと同じ名前のデータベースにアクセスしようとしてエラーになる。
:::
</details>

# MySQL と PostgreSQL のコマンド比較

## 接続と認証

|  操作  |  MySQL  |  PostgreSQL  |
|--------|---------|--------------|
| ログイン | mysql -u username -p | psql -U username -d dbname |
| データベースを指定してログイン | mysql -u username -p dbname | psql -U username -d dbname |
| 接続終了 | EXIT; または QUIT; または \q | \q |
| ユーザー一覧表示 | SELECT User FROM mysql.user; | \du |

## データベース管理

|  操作  |  MySQL  |  PostgreSQL  |
|--------|---------|--------------|
| DB一覧表示 | SHOW DATABASES; | \l または \list |
| DB切り替え | USE database_name; | \c database_name |
| DB情報表示 | SHOW CREATE DATABASE dbname; | \l+ dbname |

## テーブル管理

|  操作  |  MySQL  |  PostgreSQL  |
|--------|---------|--------------|
| テーブル一覧表示 | SHOW TABLES; | \dt |
| テーブル構造表示 | DESCRIBE table_name; | \d table_name（基本情報）<br>\d+ table_name（詳細情報） |
| インデックス一覧 | SHOW INDEX FROM table_name; | \di（全インデックス）<br>\di+ table_name（詳細情報） |

## クエリ実行

|  操作  |  MySQL  |  PostgreSQL  |
|--------|---------|--------------|
| 表示形式切替 | （デフォルトは表形式） | \x on (縦長表示) / \x off (通常表示) |
| 実行時間表示 | （組み込み機能なし） | \timing on / \timing off |
| オートコミット設定 | SET autocommit=0;（デフォルトはON） | （デフォルトはOFF） |

## データ操作

|  操作  |  MySQL  |  PostgreSQL  |
|--------|---------|--------------|
| CSVエクスポート | SELECT ... INTO OUTFILE '/path/file.csv' ...<br>※secure_file_priv設定の影響を受ける | \copy (SELECT ...) TO '/path/file.csv' WITH CSV<br>※クライアント経由でファイルに書き込み |
| CSVインポート | LOAD DATA INFILE '/path/file.csv' ... | \copy table_name FROM '/path/file.csv' WITH CSV |
| 圧縮ファイル出力 | （組み込み機能なし） | COPY (SELECT ...) TO PROGRAM 'gzip > /path/file.csv.gz' WITH CSV HEADER; |

## データ定義

| 操作 | MySQL | PostgreSQL |
|-----|--------|------------|
|自動採番|AUTO_INCREMENT|SERIAL|
|自動採番使用例|CREATE TABLE users (id INT(10) NOT NULL AUTO_INCREMENT,...|CREATE TABLE users (id SERIAL NOT NULL,...|

## ヘルプと情報

|  操作  |  MySQL  |  PostgreSQL  |
|--------|---------|--------------|
| コマンド一覧 | help contents;<br>\h （8.0未満）| \\? |
| SQL構文ヘルプ | help 'select'; | \h SELECT（大文字小文字区別なし） |
| 実行履歴表示 | （~/.mysql_historyを確認） | \s |

## PostgreSQLのみの機能

|  操作  |  PostgreSQL  |  説明  |
|--------|--------------|--------|
| スキーマ一覧 | \dn | データベース内のオブジェクトを論理的にグループ化するスキーマを表示 |
| 関数一覧 | \df<br>\df+ | ユーザー定義関数<br>システム関数も含めた詳細表示 |
| ビュー一覧 | \dv | データベース内のビューを表示 |
| 結果をファイルに保存 | \o filename / \o （終了） | クエリ結果をファイルに保存 |

## 共通SQL文

|  操作  |  共通SQL文  |  備考  |
|--------|------------|--------|
| データベース作成 | CREATE DATABASE dbname; | 構文はほぼ同じ |
| データベース削除 | DROP DATABASE dbname; | 構文は同じ |
| テーブル作成 | CREATE TABLE table_name (...); | データ型が異なる（MySQL:INT、PostgreSQL:INTEGER など） |
| テーブル削除 | DROP TABLE table_name; | 構文は同じ |
| データ検索 | SELECT * FROM table_name; | 基本的なSELECT文は同じ |
| データ挿入 | INSERT INTO table_name VALUES (...); | 構文は同じ |
| データ更新 | UPDATE table_name SET col=val WHERE ...; | 構文は同じ |
| データ削除 | DELETE FROM table_name WHERE ...; | 構文は同じ |
| ユーザー作成 | MySQL: CREATE USER 'username'@'hostname'<br>PostgreSQL: CREATE ROLE username | MySQLはホスト名指定可能<br>PostgreSQLではROLEを使用 |
| 権限付与 | GRANT privilege ON object TO user; | MySQLではALL PRIVILEGESで全権限付与<br>PostgreSQLでは個別指定が必要 |
| トランザクション開始 | BEGIN; | 構文は同じ |
| トランザクションコミット | COMMIT; | 構文は同じ |
| トランザクションロールバック | ROLLBACK; | 構文は同じ |


## 主な違いのポイント

1. PostgreSQLではバックスラッシュ(\)で始まるコマンドを使用
2. PostgreSQLは接続時にデータベース名の指定が必須
3. 基本的なSQL文（SELECT, INSERT, UPDATE, DELETE等）は両システムで共通
4. PostgreSQLでAUTO_INCREMENTは使えないのでSERIALを使用する
5. PostgreSQLはデフォルトでautocommitがOFF、MySQLはデフォルトでON
6. MySQLとPostgreSQLではユーザー管理と権限付与の方法に大きな違いがある

# さいごに
DBとしての機能など他にも色々違いはあると思いますが、今回は主にコマンドの違いをまとめました。
慣れていないコマンドも結構あるので間違いなどあれば教えてもらえると嬉しいです。