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: 終了

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
   

   

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

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. 確認のため，新規作成したロールで psql を使ってみる

   Ubuntu の場合には，下に記載の「Ubuntu での追加設定」を確認すること．

   psql -U testuser -d postgres
   \q
   

   

Ubuntu での追加設定

全ユーザ の md5 認証を有効にする．

/etc/postgresql/12/main/pg_hba.conf を書き換えて，全ユーザである all の md5 認証を有効にする．

そのために，次の１行を追加する

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;

データベース

データベースの一覧

「\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

スキーマ

スキーマの一覧

「\dn+」はすべてのスキーマの詳細情報の表示．

\dn+

スキーマの新規作成

1. スキーマの新規作成

   次のように操作する．

   create schema myschema;
   

   
2. 確認表示

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

   \dn+
   

   

カレントスキーマの確認

「show search_path;」は，カレントスキーマの表示．

show 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行を追記する． （ファイル $HOME/.psqlrc がなければ新規作成する）

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

  

9. SQL の実行，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」というエラーが出て動かない場合があるので注意．