psql の主要機能（Ubuntu 上）

1. PostgreSQL のインストール手順

• Windows環境におけるPostgreSQL 14.5，pgAdmin 4，PostGIS 3のインストール方法とpsqlによるデータベース操作: 別ページ »で詳しく解説しています．
• Ubuntu環境でのPostgreSQL 14，pgAdmin 4，PostGIS 3のインストール手順: 別ページ »で詳しく説明しています．

2. psqlの基本操作ガイド

• psql --version: psqlのバージョン情報を確認
• psql: psqlを起動
• \copy: テーブルデータのコピーを実行
• \d, \d+: テーブルおよび関連情報を表示
• \db, \db+: テーブル空間の情報を表示
• \c: データベースへの接続および現在の接続状態を確認
• \l: データベース一覧を表示
• \q: psqlを終了

3. psqlの起動と終了手順

Ubuntuにおけるpsqlの起動と終了方法

Ubuntuのサービスアカウントpostgresとpeer認証を使用して，PostgreSQLのpsqlを操作します．

1. 「sudo -u postgres psql」コマンドを実行

   sudo -u postgres psql
   

   
2. 「\c」コマンドで，現在使用中のPostgreSQLのロール名とデータベース名を確認します．

   \c
   

   
3. 「\l」コマンドでデータベースの一覧を確認します．

   利用可能なデータベースが表示されることを確認してください．

   \l
   

   
4. 「\q」コマンドでpsqlを終了します．
   

4. PostgreSQLのロール管理

ロール，既定データベース，現在のロールの確認方法

1. 「sudo -u postgres psql」を実行します．
2. 以下のコマンドでロール一覧と現在の接続情報を確認します．

   \c
   \du
   

   

新規ロールの作成とデータベース管理者権限の付与手順

1. ロールの作成と確認手順

   【参考リソース】 https://www.postgresql.org/docs/12/sql-createrole.html

   create role testuser with superuser createdb createrole login encrypted password 'hoge$#34hoge5';
   \du
   \q
   

   
2. 新規作成したロールでの動作確認

   Ubuntu環境では，以下の「Ubuntuにおける追加設定」を必ず確認してください．

   psql -U testuser -d postgres
   \q
   

   

Ubuntuにおける追加設定

全ユーザーのmd5認証を有効化します．

/etc/postgresql/12/main/pg_hba.confを編集して，全ユーザー（all）のmd5認証を有効にします．

以下の1行を追加してください．

local   all             all                                md5

この設定を行わないと，「Peer authentication failed...」というエラーメッセージが表示され，md5認証が機能しないことを示します．

pg_hda.confの変更を反映するため，PostgreSQLサーバを再起動します．

エラーメッセージが表示されないことを確認してください．

sudo pg_ctlcluster 12 main restart
sudo pg_ctlcluster 12 main status

新規ロールの動作確認

作成したロールでpsqlを起動し，「\c」コマンドで現在の接続情報を確認します． データベースは「-d postgres」オプションでpostgresを指定します．

psql -U testuser -d postgres
\c
\q

ロールの削除方法

ロールを削除する場合は，psqlで「drop role testuser;」コマンドを実行します．

5. テーブル空間の管理

テーブル空間の一覧表示

psqlで「\db+」コマンドを実行し，テーブル空間の一覧を表示します．

\db+

Ubuntuでのテーブル空間作成手順

1. テーブル空間の作成手順

   以下のコマンドを実行します．「sudo chown -R postgres /var/sqltable1」のpostgresは Ubuntuのサービスアカウント名です．

   sudo mkdir /var/sqltable1
   sudo chown -R postgres /var/sqltable1
   sudo chmod 700 /var/sqltable1
   psql -U testuser -d postgres
   create tablespace mytablespace owner testuser location '/var/sqltable1';
   \q
   

   
2. 設定の確認

   psql -U testuser -d postgres
   \db+
   \q
   

   

デフォルトテーブル空間の確認

空の場合は「pg_default」が使用されます．

show default_tablespace;

デフォルトテーブル空間の変更方法

この設定はpsqlの終了時にリセットされます．

show default_tablespace;
set default_tablespace to mytablespace;
show default_tablespace;

一時テーブル用テーブル空間の確認

【参考リソース】 https://www.postgresql.jp/document/12/html/manage-ag-tablespaces.html

show temp_tablespaces;

一時テーブル用テーブル空間の変更

「set temp_tablespaces to ・・・;」で一時テーブル用テーブル空間を変更し， 「show temp_tablespaces;」で設定を確認します．

この設定はpsqlの終了時にリセットされます．

set temp_tablespaces to mytablespace;
show temp_tablespaces;

6. データベースの管理

データベース一覧の表示

「\l+」コマンドですべてのデータベースの詳細情報を確認できます．

\l+

新規データベースの作成手順

【参考リソース】 https://www.postgresql.jp/document/12/html/manage-ag-createdb.html

1. データベースの作成

   psqlで以下のコマンドを実行します．

   create database mydb owner testuser tablespace mytablespace encoding 'UTF8';
   

   
2. 設定の確認

   「-d mydb」で新規作成したデータベースを指定し， 「\l+」ですべてのデータベースの詳細情報を確認します．

   psql -U testuser -d mydb
   \l+
   

   
   

現在の接続データベースの確認

「\c」コマンドで，現在使用中のデータベース名とロール名を確認できます．

\c

接続データベースの切り替え

「\c mydb」コマンドで，使用するデータベースをmydbに切り替えます．

\c
\c mydb
\c

7. スキーマの管理

スキーマ一覧の表示

「\dn+」コマンドですべてのスキーマの詳細情報を表示します．

\dn+

新規スキーマの作成手順

1. スキーマの作成

   以下のコマンドを実行します．

   create schema myschema;
   

   
2. 設定の確認

   「\dn+」ですべてのスキーマの詳細情報を確認します．

   \dn+
   

   

現在のスキーマの確認

「show search_path;」コマンドで，現在のスキーマを表示します．

show search_path;

現在のスキーマの変更

「set search_path to myschema;」コマンドで，現在のスキーマを myschemaに変更します．

「show search_path;」コマンドで設定を確認します．

この設定はpsqlの終了時にリセットされます．

show search_path;
set search_path to myschema;
show search_path;

8. デフォルト設定の構成

• デフォルトのロール名とデータベース名の設定

  $HOME/.bashrcに以下の2行を追加します．

  export PGDATABASE=mydb
  export PGUSER=testuser
  

  

  設定後，「source $HOME/.bashrc」を実行して反映させます．

  
• デフォルトのテーブル空間， 一時テーブル用テーブル空間， スキーマの設定

  $HOME/.psqlrcファイルに以下の3行を追加します． （ファイルが存在しない場合は新規作成します）

  set default_tablespace to mytablespace;
  set temp_tablespaces to mytablespace;
  set search_path to myschema;
  

  

9. SQLの実行とファイル出力

テーブルの定義とデータ登録

「\dt+」コマンドで，すべてのテーブルの詳細情報を表示します．

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 );
\dt+

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

実行画面：

10. psqlの拡張オプション

データベース一覧の取得

「psql -U testuser -l」コマンドでもデータベース一覧を表示できます．

SQL実行結果のファイル出力

psql起動時に「-L ＜ファイル名＞」オプションを指定します．

SQLファイルの実行

psql起動時に「-f ＜ファイル名＞」オプションを指定します．

11. テーブルデータの入出力

テーブルデータのインポート・エクスポートには，PostgreSQLのcopyコマンドを使用します． Windows環境でファイルを扱う際は，以下の2点に注意が必要です．

• インポートの例

  copy WORK from E'd:\\KEN_ALL_UTF8.csv' with csv;
  

  * バックスラッシュを使用するため，文字列の前にエスケープ文字「E」を付加します．

• エクスポートの例

  copy ZIP2 to E'C:\\data\\zip.csv' with CSV;
  

  * バックスラッシュを使用するため，文字列の前にエスケープ文字「E」を付加します．

  出力先のディレクトリに書き込み権限がない場合， 「ERROR: could not open file ... for writing: Permission denied」というエラーが発生しますので注意が必要です．