PostgreSQLと向き合うための 現場で使えるデータベース操作・SQLノート

MySQLと向き合うための 現場で使えるデータベース操作・SQLノート のPostgreSQL編です。CLIでのPostgreSQLプロンプト操作はMySQLのものと大きく異なりますので、そこら辺を中心に備忘録していきます。

  1. macOSにPostgreSQLをセットアップする
    1. HomebrewでPostgreSQLをインストール
    1. PostgreSQLサービスの起動と自動起動の設定
    1. デフォルトのデータベースを作成
    1. psqlコマンドの確認
    1. データベースの作成
  2. コマンドの説明
    1. 一般
    1. ヘルプ
    1. Query Buffer
    1. Input/Output
    1. Conditional
    1. Informational
    1. Formatting
    1. ページャを無効化
  3. SQLファイルをデータベースに流し込む
  4. 特定のテーブルのカラム構成を確認したい
  5. ネットワーク越しのpostgresqlに接続する

macOSにPostgreSQLをセットアップする

macOSにPostgreSQLをセットアップするまでの手順を以下に示します。

HomebrewでPostgreSQLをインストール

Homebrewを使ってPostgreSQLをインストールします。

zsh
brew install postgresql

バージョンの確認:

zsh
$ psql --version
psql (PostgreSQL) 14.12 (Homebrew)

PostgreSQLサービスの起動と自動起動の設定

インストール後、PostgreSQLサービスを起動し、自動起動を設定します。

zsh
brew services start postgresql

デフォルトのデータベースを作成

まず、/usr/local/varディレクトリを作成し、適切な権限を設定します。

zsh
sudo mkdir -p /usr/local/var/postgres
sudo chown -R $(whoami) /usr/local/var/postgres

デフォルトのデータベースを作成します。

zsh
initdb /usr/local/var/postgres

psqlコマンドの確認

PostgreSQLのインストールが正常に完了したかを確認するために、psqlコマンドを実行します。

zsh
psql postgres

このコマンドでPostgreSQLプロンプトに切り替われば、インストールは成功です。プロンプトを終了するには \q を入力します。

データベースの作成

デフォルトのユーザー名は、macOSのユーザー名と同じになります。以下のコマンドで、新しいデータベースやユーザーを作成することができます。

データベースを作成します。

zsh
createdb mydatabase

ユーザーを作成します。

zsh
createuser aragi

一旦 \q でプロンプトを終了させてから、psqlを使ってデータベースに接続します。

zsh
psql -d mydatabase -U aragi

コマンドの説明

PostgreSQLプロンプト上で、 \? を使ってコマンドの説明を参照してみます。

一般

コマンド説明
\copyrightPostgreSQLの使用と配布条件を表示
\crosstabview [COLUMNS]クエリを実行し、クロス集計結果を表示
\errverbose最も最近のエラーメッセージを最大の詳細度で表示
\g [(OPTIONS)] [FILE]クエリを実行し（結果をファイルやパイプに送信）；引数なしの\gはセミコロンと同じ
\gdescクエリの結果を実行せずに説明
\gexecクエリを実行し、その結果の各値を実行
\gset [PREFIX]クエリを実行し、その結果をpsql変数に保存
\gx [(OPTIONS)] [FILE]\gと同じだが、強制的に拡張出力モード
\qpsqlを終了
\watch [SEC]指定秒毎にクエリを実行

ヘルプ

コマンド説明
\? [commands]バックスラッシュコマンドのヘルプを表示
\? optionspsqlコマンドラインオプションのヘルプを表示
\? variables特殊変数のヘルプを表示
\h [NAME]SQLコマンドの構文のヘルプを表示、* ですべてのコマンド

Query Buffer

コマンド説明
\e [FILE] [LINE]外部エディタでクエリバッファ（またはファイル）を編集
\ef [FUNCNAME [LINE]]外部エディタで関数定義を編集
\ev [VIEWNAME [LINE]]外部エディタでビュー定義を編集
\pクエリバッファの内容を表示
\rクエリバッファをリセット（クリア）
\s [FILE]履歴を表示またはファイルに保存
\w FILEクエリバッファをファイルに書き出し

Input/Output

コマンド説明
\copy ...SQL COPYを使ってデータをクライアントホストにストリーム
\echo [-n] [STRING]文字列を標準出力に書き出し（-nで改行なし）
\i FILEファイルからコマンドを実行
\ir FILE\iと同じだが、現在のスクリプトの場所に相対的
\o [FILE]すべてのクエリ結果をファイルやパイプに送信
\qecho [-n] [STRING]文字列を\o出力ストリームに書き出し（-nで改行なし）
\warn [-n] [STRING]文字列を標準エラー出力に書き出し（-nで改行なし）

Conditional

コマンド説明
\if EXPR条件付きブロックを開始
\elif EXPR現在の条件付きブロック内の代替
\else現在の条件付きブロック内の最終代替
\endif条件付きブロックを終了

Informational

コマンド説明
\d[S+]テーブル、ビュー、シーケンスを一覧表示
\d[S+] NAMEテーブル、ビュー、シーケンス、インデックスを説明
\da[S] [PATTERN]集約関数を一覧表示
\dA[+] [PATTERN]アクセスメソッドを一覧表示
\dAc[+] [AMPTRN [TYPEPTRN]]オペレータクラスを一覧表示
\dAf[+] [AMPTRN [TYPEPTRN]]オペレータファミリを一覧表示
\dAo[+] [AMPTRN [OPFPTRN]]オペレータファミリのオペレータを一覧表示
\dAp[+] [AMPTRN [OPFPTRN]]オペレータファミリのサポート関数を一覧表示
\db[+] [PATTERN]テーブルスペースを一覧表示
\dc[S+] [PATTERN]変換を一覧表示
\dC[+] [PATTERN]キャストを一覧表示
\dd[S] [PATTERN]他の場所に表示されないオブジェクトの説明を表示
\dD[S+] [PATTERN]ドメインを一覧表示
\ddp [PATTERN]デフォルトの権限を一覧表示
\dE[S+] [PATTERN]外部テーブルを一覧表示
\des[+] [PATTERN]外部サーバを一覧表示
\det[+] [PATTERN]外部テーブルを一覧表示
\deu[+] [PATTERN]ユーザーマッピングを一覧表示
\dew[+] [PATTERN]外部データラッパーを一覧表示
\df[anptw][S+] [FUNCPTRN [TYPEPTRN ...]][集約/通常/プロシージャ/トリガ/ウィンドウ]関数を一覧表示
\dF[+] [PATTERN]テキスト検索構成を一覧表示
\dFd[+] [PATTERN]テキスト検索辞書を一覧表示
\dFp[+] [PATTERN]テキスト検索パーサーを一覧表示
\dFt[+] [PATTERN]テキスト検索テンプレートを一覧表示
\dg[S+] [PATTERN]ロールを一覧表示
\di[S+] [PATTERN]インデックスを一覧表示
\dl大規模オブジェクトを一覧表示、\lo_listと同じ
\dL[S+] [PATTERN]手続き型言語を一覧表示
\dm[S+] [PATTERN]マテリアライズドビューを一覧表示
\dn[S+] [PATTERN]スキーマを一覧表示
\do[S+] [OPPTRN [TYPEPTRN [TYPEPTRN]]]オペレータを一覧表示
\dO[S+] [PATTERN]コレーションを一覧表示
\dp [PATTERN]テーブル、ビュー、シーケンスのアクセス権限を一覧表示
\dP[itn+] [PATTERN][インデックス/テーブル] パーティショニングを一覧表示 [n=ネスト]
\drds [ROLEPTRN [DBPTRN]]データベース毎のロール設定を一覧表示
\dRp[+] [PATTERN]レプリケーション公開を一覧表示
\dRs[+] [PATTERN]レプリケーション購読を一覧表示
\ds[S+] [PATTERN]シーケンスを一覧表示
\dt[S+] [PATTERN] テーブルを一覧表示
\dT[S+] [PATTERN]データ型を一覧表示
\du[S+] [PATTERN]ロールを一覧表示
\dv[S+] [PATTERN]ビューを一覧表示
\dx[+] [PATTERN]拡張機能を一覧表示
\dX [PATTERN]拡張統計を一覧表示
\dy[+] [PATTERN]イベントトリガーを一覧表示
\l[+] [PATTERN]データベースを一覧表示
\sf[+] FUNCNAME関数の定義を表示
\sv[+] VIEWNAMEビューの定義を表示
\z [PATTERN]\dpと同じ

Formatting

コマンド説明
\a整列されていない出力モードと整列された出力モードを切り替え
\C [STRING]テーブルタイトルを設定、なしの場合は解除
\f [STRING]整列されていないクエリ出力のフィールドセパレータを表示または設定
\HHTML出力モードを切り替え（現在オフ）
\pset [NAME [VALUE]]テーブル出力オプ

ページャを無効化

psqlのプロンプトで検索結果をlessで表示している際に、一気に最後まで表示するには、以下のコマンドを使用します。
bash
\pset pager off

これでページャが無効化され、一気に全ての結果が表示されるようになります。

SQLファイルをデータベースに流し込む

以下のコマンドを使用して、PostgreSQLデータベースにSQLファイルを流し込むことができます。

sh
psql -U ユーザー名 -d データベース名 -f ファイルパス.sql

特定のテーブルのカラム構成を確認したい

以下のSQLクエリを使用して、特定のテーブルのカラム構成を確認できます。

sql
SELECT column_name, data_type, character_maximum_length
FROM information_schema.columns
WHERE table_name = 'テーブル名';

ネットワーク越しのpostgresqlに接続する

macOSからLAN内のRocky Linuxで動作しているPostgreSQLに接続する方法です。

PostgreSQLの設定ファイル（通常は/var/lib/pgsql/バージョン/data/postgresql.conf）を開き、外部からの接続を許可するように設定を変更します。

postgresql.conf
listen_addresses = '*'
pg_hba.confファイル（通常は/var/lib/pgsql/バージョン/data/pg_hba.conf）を編集し、外部接続を許可するIP範囲を設定します。
pg_hba.conf
host    all             all             192.168.1.0/24          md5

ここで192.168.1.0/24は許可するIPアドレスの範囲です。私のAndroidテザリング下のLANの場合、192.168..の範囲で変わるのでを許可するには、以下のように設定しました。

pg_hba.conf
host    all             all             192.168.0.0/16          md5

ここで、192.168.0.0/16は192.168..全体をカバーするCIDR表記です。この設定により、192.168.0.0から192.168.255.255までの範囲のIPアドレスからの接続が許可されます。

以上の設定を反映するために、PostgreSQLサービスを再起動します。

shell
sudo systemctl restart postgresql

ファイアーウォールを使っている場合は、適宜ポートを解放してください。 これでmacOS上のpsqlクライアントコマンドや、DataGrip.appなどから直接外部DBへ接続可能になりました。

関連記事

アイデアノート > 開発関連
最後までご覧いただきありがとうございます！

▼ 記事に関するご質問やお仕事のご相談は以下よりお願いいたします。
お問い合わせフォーム