psql の主要機能(Windows 上)
psql は,PostgreSQL データベースの管理と操作を効率的に実行できるコマンドラインツールである.
Ubuntu での詳細については,別ページ »を参照.
【サイト内の主な PostgreSQL 関連ページ】
- Windows 環境における PostgreSQL 14.5,pgAdmin 4,PostGIS 3 のインストール手順と,psql を使用したテーブル定義およびレコード操作について: 別ページ »で解説
- Ubuntu 環境での PostgreSQL 14,pgAdmin 4,PostGIS 3 のインストール方法: 別ページ »で解説
- PostgreSQL の実践的な活用方法: 別ページ »に体系的にまとめている.
【関連する外部ページ】
1. 前準備
PostgreSQL のインストール
- Windows 環境における PostgreSQL 14.5,pgAdmin 4,PostGIS 3 のインストール手順と,psql を使用したテーブル定義およびレコード操作について: 別ページ »で解説
- Ubuntu 環境での PostgreSQL 14,pgAdmin 4,PostGIS 3 のインストール方法: 別ページ »で解説
あらかじめ決めておく事項
このページでは,データベースを新規作成するため, データベース名と文字コード(エンコーディング)を事前に決定する必要がある. 本ページでは,以下の設定を使用する.
- データベース名: mydb
データベース名は,半角英数字および記号(スペースを除く)を使用して任意に設定可能である.
- 文字コード(エンコーディング): UTF8
また,データベース・一般ユーザのアカウントを作成するため, ユーザ名とパスワードを以下のように設定する.
- ユーザ名: testuser
- パスワード: hoge$#34hoge5
* セキュリティ確保のため,実際の運用では異なるパスワードを設定すること
データベース・一般ユーザのアカウント作成およびデータベースの作成と権限の設定には, PostgreSQL データベース管理者の認証情報が必要となるため,事前に確認しておく. 本ページでは,PostgreSQL データベース管理者のユーザ名を pgadmin として説明する.
2. psql の基本操作
- psql --version: psql のバージョン情報表示
- psql: psql の起動
- \copy: テーブルデータのインポート・エクスポート
- \d, \d+: テーブル構造の詳細表示
- \db, \db+: テーブル空間の一覧表示
- \c: データベース接続の切り替えおよび現在の接続状態確認
- \l: データベース一覧の表示
- \q: psql の終了
3. psql の起動と終了
psql コマンド
- Windows のコマンドプロンプトを起動
- psql -U postgresを実行
「postgres」は PostgreSQL データベース管理者のユーザ名を指定
psql -U postgres
- PostgreSQL データベース管理者のパスワード入力
プロンプトが表示されたら, PostgreSQL データベース管理者 (postgres) のパスワードを入力する.
セキュリティ上の理由により,パスワード入力時に画面上には表示されない.
- 「\c」コマンドで,現在の PostgreSQL ロール名と接続中のデータベース名を確認.
\c
- 「\l」コマンドで,データベース一覧を確認
システムデータベースである postgres,template0,template1 の存在を確認.
\l
- 「\q」コマンドで psql を終了
SQL Shell の利用
Windows のスタートメニューから, 「PostgreSQL 12」→「SQL Shell (psql)」を選択する.

接続設定では,以下の情報を入力する.
- Server [localhost]: Enter キー
- Database [postgres]: 接続先データベース名
- Port [5432]: Enter キー
- Username [postgres]: Enter キー
- Client Encoding [SJIS]: Enter キー
- ユーザ postgres のパスワード: PostgreSQL インストール時に設定したパスワード

4. PostgreSQL での一般ユーザアカウント管理
ロール,デフォルトデータベース,現在のロール確認
- 「psql -U postgres」でログイン
- 次のコマンドでロール一覧と現在の接続状態を確認
\c \du
アカウント作成
新規アカウントはcreate userコマンドで作成する.
-
ユーザ名testuserのアカウントを作成する場合:
create user testuser with password 'hoge$#34hoge5' createdb;
- 作成したアカウントで psql にログインし,動作確認を行う.
アカウントの削除
不要なアカウントは「drop user testuser」コマンドで削除できる.
5. データベースの作成
データベースの作成にはcreate databaseコマンドを使用する.- mydbという名前のデータベースを作成する例:
文字コード(エンコーディング)はUTF8を指定
コマンド実行後,エラーメッセージが表示されないことを確認
create database mydb encoding 'UTF8';
- -E UTF8 : データベースの文字エンコーディング指定
- mydb : 作成するデータベース名
6. SQL の実行とファイル出力
テーブル定義とデータ挿入
create table commodity (
type integer primary key not null,
name text not null,
price integer);
insert into commodity values( 1, 'apple', 50 );
insert into commodity values( 2, 'orange', 20 );
insert into commodity values( 3, 'strawberry', 100 );
insert into commodity values( 4, 'watermelon', 150 );
insert into commodity values( 5, 'melon', 200 );
insert into commodity values( 6, 'banana', 100 );

SQL クエリの実行
select * from commodity;
実行結果の例:
TYPE NAME PRICE ---- ---------- ----- 1 apple 50 2 orange 20 3 strawberry 100 4 watermelon 150 5 melon 200 6 banana 100
実行画面:

7. psql のオプション機能
データベース一覧の表示
「psql -U pgadmin -l」コマンドでデータベース一覧を表示する. Windows 環境ではコマンドプロンプトから実行する.
SQL 実行結果のファイル出力
psql 起動時に「-L <出力ファイル名>」オプションを指定する.
SQL ファイルの実行
psql 起動時に「-f <入力ファイル名>」オプションを指定する.
テーブルのインポート,エクスポート
テーブルデータの入出力にはPostgreSQL の copy コマンドを使用する. Windows 環境では特に注意が必要.
- データインポートの例
copy WORK from E'd:\\KEN_ALL_UTF8.csv' with csv;
* バックスラッシュを使用するため,文字列の前にエスケープ文字を示す「E」を付加する.
- データエクスポートの例
copy ZIP2 to E'C:\\Program Files\\PostgreSQL\\8.3\\data\\zip.csv' with CSV;
* バックスラッシュを使用するため,文字列の前にエスケープ文字を示す「E」を付加する.
* PostgreSQL の data ディレクトリ(PostgreSQL のインストールディレクトリ配下の C:\\Program Files\\PostgreSQL\\8.3\\data\\)以外を出力先として指定すると, 「ERROR: could not open file ... for writing: Permission denied」というエラーが発生するため,注意が必要である.