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

はじめに

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

前提

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

Docker-compose.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:

DBにアクセスする際にパスワードが不要な設定にしています。
開発環境でのみ使用するようにしてください。

DBコンテナに入る方法

今回の設定では以下のコマンドでDB内に入れます。

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」というデータベース

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

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（基本情報） \d+ table_name（詳細情報）
インデックス一覧	SHOW INDEX FROM table_name;	\di（全インデックス） \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' ... ※secure_file_priv設定の影響を受ける	\copy (SELECT ...) TO '/path/file.csv' WITH CSV ※クライアント経由でファイルに書き込み
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; \h （8.0未満）	\?
SQL構文ヘルプ	help 'select';	\h SELECT（大文字小文字区別なし）
実行履歴表示	（~/.mysql_historyを確認）	\s

PostgreSQLのみの機能

操作	PostgreSQL	説明
スキーマ一覧	\dn	データベース内のオブジェクトを論理的にグループ化するスキーマを表示
関数一覧	\df \df+	ユーザー定義関数 システム関数も含めた詳細表示
ビュー一覧	\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' PostgreSQL: CREATE ROLE username	MySQLはホスト名指定可能 PostgreSQLではROLEを使用
権限付与	GRANT privilege ON object TO user;	MySQLではALL PRIVILEGESで全権限付与 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としての機能など他にも色々違いはあると思いますが、今回は主にコマンドの違いをまとめました。
慣れていないコマンドも結構あるので間違いなどあれば教えてもらえると嬉しいです。