とほほのPostgreSQL入門

目次

PostgreSQLとは

インストール

下記を参照してインストールしてください。

# Docker
$ sudo docker run --name my-postgres -e POSTGRES_PASSWORD=my-password -d postgres
$ sudo docker exec -it my-postgres psql -h localhost -U postgres -d postgres
$ sudo docker exec -it my-postgres /bin/bash
$ psql -h localhost -U postgres -d postgres

# Ubuntu 22.04
sudo apt -y install lsb-release wget gnupg
sudo sh -c 'echo "deb https://apt.postgresql.org/pub/repos/apt \
  $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get -y install postgresql
sudo -u postgres psql

# RHEL9/AlmaLinux9/Rocky Linux9
sudo dnf install -y \
  https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf -qy module disable postgresql
sudo dnf install -y postgresql16-server
sudo /usr/pgsql-16/bin/postgresql-16-setup initdb
sudo systemctl enable postgresql-16
sudo systemctl start postgresql-16
sudo -u postgres psql

psqlコマンド

psql は PostgreSQL に接続するクライアントコマンドです。-h で接続先サーバを、-U でユーザIDを、-d でデータベース名を指定します。ユーザID を省略すると OS のログインユーザID でアクセスします。

$ psql -h localhost -U postgres -d postgres
psql (16.1)
Type "help" for help.

postgres=#

終了するには \quit を使用します。

postgres=# \quit

SQL(DDL/DML/DCL)

PostgreSQL でデータを管理するには SQL(Structured Query Language) を用います。SQL では様々なコマンド群がサポートされいますが、下記に大別されます。

DDL(Data Definition Language) はデータベース、スキーマ、テーブルなどを作成・削除するコマンド群で CREATE, DROP, ALTER などがあります。

DML(Data Manipulation Language) はデータを参照・挿入・削除・更新するコマンド群で SELECT, INSERT, DELETE, UPDATE などがあります。

DCL(Data Control Language) はユーザ権限を設定したり、トランザクションを制御するコマンド群で GRANT, REVOKE, BEGIN, COMMIT, ROLLBACK などがあります。

ユーザ

ユーザ一覧を表示する(\du)

\du
SELECT USENAME FROM pg_user;

ユーザを作成する(CREATE USER)

CREATE USER yamada;

ユーザを削除する(DROP USER)

DROP USER yamada;

パスワードを変更する(ALTER USER WITH PASSWORD)

ALTER USER yamada WITH PASSWORD 'yamada123';

ロール

PostgreSQL ではユーザとロールはほぼ同じものと考えて構いません。ユーザを作成すると同名のロールも作成されます。ロールを作成しても同名のユーザが作成されます。CREATE USER と CREATE ROLE の違いは、CREATE USER が作成したユーザ(ロール)に LOGIN 権限がつくのに対して、CREATE ROLE で作成したユーザ(ロール) には NOLOGIN 権限となりログインできない点が異なります。

ロール一覧を表示する(\du)

\du
SELECT USENAME FROM pg_roles;

ロールを作成する(CREATE ROLE)

CREATE ROLE myrole;

ロールを削除する(DROP ROLE)

DROP ROLE myrole;

データベース

PostgreSQL ではひとつのデータベースクラスタ中に複数のデータベースを持つことができます。デフォルトでは下記のデータベースが作成されています。

postgres
template0
template1

postgres はデフォルトで使用されるデータベースです。create database で他のデータベースを作成することもできます。template1 は create database でデータベースを作成する際にテンプレートとして使用されるデータベースです。template1 にテーブルを作成しておくと、新規データベース作成時にも同じテーブルが作成されます。template0 は、template1 に何かテーブルが作成された状態で空のデータベースを作成したい時に使用します。create database の --template オプションに指定します。

データベースの一覧を表示する(\l)

\l
SELECT * FROM pg_database;

データベースを作成する(CREATE DATABASE)

CREATE DATABASE mydb;

データベースを削除する(DROP DATABASE)

DROP DATABASE mydb;

データベースのオーナーを変更する(ALTER DATABASE OWNER TO)

ALTER DATABASE mydb OWNER TO yamada;

データベースを変更する(\c)

postgres=# \c mydb;
mydb=#

スキーマ

ひとつのデータベースの中に複数のスキーマを作成することができます。デフォルトでは public という名前のスキーマが作成されています。スキーマ名を省略した場合は public スキーマが使用されます。

public

スキーマの一覧を表示する(\dn)

\dn
\dn+
SELECT * FROM information_schema.schemata;

スキーマを作成する(CREATE SCHEMA)

CREATE SCHEMA myschema;

スキーマを削除する(DROP SCHEMA)

DROP SCHEMA myschema;

スキーマのオーナーを変更する(ALTER SCHEMA OWNER TO)

ALTER SCHEMA myschema OWNER TO yamada;

カレントスキーマを表示する(current_schema())

SELECT current_schema();
SHOW search_path;

カレントスキーマを変更する(SET search_path TO)

スキーマは検索順序で指定します。下記の様に指定すると、テーブルを探す際に schema1 → schema2 の順序で探します。

SET search_path TO schema1, schema2;

テーブル

ひとつのスキーマの中に複数のテーブルを作成することができます。

テーブルの一覧を表示する(\dt)

\dt
\dt myschema.*
SELECT * FROM pg_tables;

テーブルを作成する(CREATE TABLE)

CREATE TABLE mytable ( id text, name text );
CREATE TABLE myschema.mytable ( id text, name text );

テーブルを削除する(DROP TABLE)

DROP TABLE mytable;
DROP TABLE myschema.mytable;

テーブルのカラム一覧を表示する(\d)

\d mytable;
\d myschema.mytable;

テーブルにカラムを追加する(ALTER TABLE ADD)

ALTER TABLE mytable ADD new_column text;

カラム名を変更する(ALTER TABLE RENAME)

ALTER TABLE mytable RENAME old_name TO new_name;

カラムを削除する(ALTER TABLE DROP)

ALTER TABLE mytable DROP column_name;

テーブルを空にする(TRUNCATE)

TRUNCATE mytable;

ビュー

ビューを作成する(CREATE VIEW)

CREATE VIEW は他のテーブルの値を SELECT した結果を仮想的なテーブル(ビュー)として定義します。

CREATE TABLE users (id text, name text);
CREATE TABLE points (id text, date date, point integer);

CREATE VIEW points_view (id, name, dare, point) AS
 SELECT p.id, u.name, p.date, p.point
  FROM points AS p
  LEFT JOIN users AS u ON u.id = p.id;

SELECT * FROM points_view;

ビューを削除する(DROP VIEW)

DTOP VIEW points_view;

ビューの一覧を表示する(\dv)

\dv
SELECT schemaname, viewname, viewowner FROM pg_views;

ビューを編集する(\ev)

\ev points_view

データ操作

参照(SELECT)

SELECT * FROM users;

WHERE は検索条件を指定します。

SELECT * FROM users WHERE user_id = 'U12345';

ORDER BY はソート条件を指定します。

SELECT * FROM users ORDER BY user_id DESC;

LIMIT は表示する最大件数を指定します。

SELECT * FROM users LIMIT 10;

OFFSET は検索を開始する位置を指定します。

SELECT * FROM users ORDER BY user_id OFFSET 10 LIMIT 10;

count() はレコードの件数を表示します。

SELECT count(*) FROM users;

distinct() は重複したデータを排除した結果を出力します。

SELECT distinct(id) FROM users;

GROUP BY は指定したカラムでグルーピングし、個数(count)、平均値(avg)、最大値(max)、最小値(min)などの集約関数の結果を出力します。

SELECT id, avg(point) AS average FROM points GROUP BY id;

WHERE は GROUP BY による集約を行う前に抽出を行いますが、HAVING は GROUP BY による集約を行った後に抽出を行います。

SELECT id, avg(point) AS average FROM points GROUP BY id HAVING id = 'U001';

挿入(INSERT)

INSERT INTO users ( user_id, user_name ) VALUES ( 'U12345', 'Yamada' );

更新(UPDATE)

UPDATE users SET user_name = 'Tanaka' WHERE user_id = 'U12345';

削除(DELETE)

DELETE FROM users WHERE user_id = 'U12345';

あれば更新・なければ挿入(UPSERT)

該当するレコードがあれば更新(UPDATE)し、なければ挿入(INSERT)する仕組みで、属に UPSERT と呼ばれます。重複を判断するカラムは PRIMARY KEY か UNIQUE 制約のカラムである必要があります。

CREATE TABLE users (id text, name text, PRIMARY KEY(id));
INSERT INTO users VALUES ('U123', 'Yamada');
INSERT INTO users VALUES ('U123', 'Tanaka') 
  ON CONFLICT(id)
  DO UPDATE SET name = 'Tanaka';

PostgreSQL 15 からは MERGE 文も使用できるようになりました。

MERGE INTO users AS a
  USING (VALUES ('U123', 'Suzuki')) AS b(id, name) ON a.id = b.id
  WHEN MATCHED THEN
    UPDATE SET name = b.name
  WHEN NOT MATCHED THEN
    INSERT (id, name) VALUES (b.id, b.name;

結合(JOIN)

JOIN は複数のテーブルを結合します。下記の結合方法があります。FROM に指定するのが左テーブル、JOIN で指定するのが右テーブルと呼ばれます。確認のために下記のような左テーブル(points)、右テーブル(users)を用意します。

CREATE TABLE users (id text, name text);
CREATE TABLE points (id text, point integer);
INSERT INTO users VALUES ('U001', 'Yamada'), ('U002', 'Tanaka'), ('U003', 'Suzuki');
INSERT INTO points VALUES ('U001', 82), ('U002', 63), ('U004', 58);

SELECT * FROM points;   -- 左テーブルには U001, U002, U004 がある
  id  | point
------+-------
 U001 |    82
 U002 |    63
 U004 |    58

SELECT * FROM users;   -- 右テーブルには U001, U002, U003 がある
  id  |  name
------+--------
 U001 | Yamada
 U002 | Tanaka
 U003 | Suzuki

内部結合(JOIN)

JOIN (=INNER JOIN)は内部結合と呼ばれ、左テーブル・右テーブル両方にあるレコードが出力されます。

SELECT * FROM points AS p JOIN users AS u ON u.id = p.id;
  id  | point |  id  |  name
------+-------+------+--------
 U001 |    82 | U001 | Yamada
 U002 |    63 | U002 | Tanaka

左外部結合(LEFT JOIN)

LEFT JOIN(=LEFT OUTER JOIN)は左外部結合と呼ばれ、左テーブル(points)にあるレコードのみが出力されます。

SELECT * FROM points AS p LEFT JOIN users AS u ON u.id = p.id;
  id  | point |  id  |  name
------+-------+------+--------
 U001 |    82 | U001 | Yamada
 U002 |    63 | U002 | Tanaka
 U004 |    58 |      |

右外部結合(RIGHT JOIN)

RIGHT JOIN(=RIGHT OUTER JOIN)は右外部結合と呼ばれ、右テーブル(users)にあるレコードのみが出力されます。

SELECT * FROM points AS p RIGHT JOIN users AS u ON u.id = p.id;
  id  | point |  id  |  name
------+-------+------+--------
 U001 |    82 | U001 | Yamada
 U002 |    63 | U002 | Tanaka
      |       | U003 | Suzuki

完全外部結合(FULL JOIN)

FULL JOIN(=FULL OUTER JOIN)は右外部結合と呼ばれ、左テーブル(points)または右テーブル(users)にあるレコードがすべて出力されます。

SELECT * FROM points AS p FULL JOIN users AS u ON u.id = p.id;
  id  | point |  id  |  name
------+-------+------+--------
 U001 |    82 | U001 | Yamada
 U002 |    63 | U002 | Tanaka
      |       | U003 | Suzuki
 U004 |    58 |      |

クロス結合(CROSS JOIN)

CROSS JOIN は左テーブルと右テーブルの完全な組み合わせを出力します。

SELECT * FROM points CROSS JOIN users;
  id  | point |  id  |  name
------+-------+------+--------
 U001 |    82 | U001 | Yamada
 U001 |    82 | U002 | Tanaka
 U001 |    82 | U003 | Suzuki
 U002 |    63 | U001 | Yamada
 U002 |    63 | U002 | Tanaka
 U002 |    63 | U003 | Suzuki
 U004 |    58 | U001 | Yamada
 U004 |    58 | U002 | Tanaka
 U004 |    58 | U003 | Suzuki

結果結合(UNION)

UNION を用いると複数の SELECT の結果を縦方向に結合することができます。

CREATE TABLE users_A (id text, name text);
CREATE TABLE users_B (id text, name text);
INSERT INTO users_A VALUES ('U001', 'Yamada'), ('U002', 'Tanaka');
INSERT INTO users_B VALUES ('U003', 'Sasaki'), ('U004', 'Suzuki');
SELECT * FROM users_A UNION SELECT * FROM users_B;
  id  |  name
------+--------
 U001 | Yamada
 U002 | Tanaka
 U003 | Sasaki
 U004 | Suzuki

トランザクション

トランザクション(BIGIN, COMMIT, ROLLBACK)

例えば、Aさんのポイントを 10ポイント Bさんに移す場合、(1)Aさんのポイントを10減算する、(2)Bさんのポイントを10加算する という処理を行います。この際、(1) の処理を行ったあと (2) の処理を行う間は不整合な状態にあります。(1) と (2) の両方を行ってからまとめてデータベースに反映するためにはトランザクションを用います。BEGIN はトランザクションを開始し、COMMIT はトランザクションの結果をまとめてデータベースに書き込みます。

DROP TABLE IF EXISTS users;
CREATE TABLE users (
  id text,
  name text,
  point int,
  PRIMARY KEY(id)
);
INSERT INTO users VALUES ('U001', 'Yamada', 100);
INSERT INTO users VALUES ('U002', 'Tanaka', 100);
BEGIN;
UPDATE users SET point = point - 10 WHERE id = 'U001';  -- (1)
UPDATE users SET point = point + 10 WHERE id = 'U002';  -- (2)
COMMIT;
SELECT * FROM users;

ROLLBACK はトランザクションを破棄してトランザクション開始前の状態に戻します。

BEGIN;
UPDATE users SET point = point - 10 WHERE id = 'U001';  -- (1)
UPDATE users SET point = point + 10 WHERE id = 'U002';  -- (2)
ROLLBACK;

行レベルロック(FOR UPDATE/SHARE)

SELECT FOR UPDATE または SELECT FOR SHARE を用いて SELECT した対象のレコードをロックすることができます。ロックしている間、他のプロセスは禁止された操作がブロックされます。(NOWAIT をつけた場合はエラー)

BEGIN;
SELECT * FROM users WHERE id = 'U001' FOR UPDATE;
UPDATE users SET point = 100 WHERE id = 'U001';
COMMIT;

テーブルロック(LOCK)

テーブル単位でロックするには LOCK を用います。

BEGIN;
LOCK users;
 :
COMMIT;

ACCESS SHARE から ACCESS EXCLUSIVE まで様々なロックモードがサポートされています。詳細は 「明示的ロック」を参照してください。

LOCK users IN ACCESS EXCLUSIVE MODE

権限付与(GRANT)

テーブルに対する権限を追加する(GRANT)

ユーザ(ロール)に対してテーブルに対する権限を付与します。権限には SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER を指定できます。

GRANT ALL PRIVILEGES ON users TO yamada;
GRANT SELECT, INSERT, UPDATE, DELETE ON users TO yamada;

スキーマに含まれるすべてのテーブルに対する権限を与えるには次のようにします。

GRANT SELECT ON ALL TABLES IN SCHEMA myschema TO yamada;

権限を削除する(REVOKE)

権限を削除するには REVOKE を用います。

REVOKE SELECT ON users FROM yamada;
REVOKE ALL PRIVILEGES ON users FROM yamada;
REVOKE SELECT ON ALL TABLES IN SCHEMA public FROM yamada;

プロシージャ

手続きをまとめて実行するプロシージャをサポートしています。開発言語としては PL/pgSQL、PL/Tcl、PL/Perl、PL/Python の他、JavaScript(v8)、C言語などもサポートされています。

プロシージャを作成する(CREATE PROCEDURE)

下記の様にプロシージャを定義することができます。

CREATE PROCEDURE add_user(id text, name text)  -- add_userプロシージャを定義
LANGUAGE plpgsql                               -- 言語を指定
AS $$                                          -- $$までの間がプロシージャ定義
BEGIN
  INSERT INTO users VALUES (id, name);
END
$$;                                            -- プロシージャ定義の終わり

プロシージャは下記の様に呼び出すことができます。

CREATE TABLE users (id text, name text);
CALL add_user('U001', 'Yamada');
CALL add_user('U002', 'Tanaka');
CALL add_user('U003', 'Suzuki');
SELECT * FROM users;

プロシージャの一覧を表示する(\df)

\df

プロシージャを削除する(DROP PROCEDURE)

DROP PROCEDURE add_user;

関数

関数を作成する(CREATE FUNCTION)

下記の様に関数を定義することができます。プロシージャと似ていますが、戻り値を返すことができる点が異なります。

CREATE FUNCTION add_func(x int, y int)  -- add()関数を定義
RETURNS integer                         -- 戻り値の型はinteger
LANGUAGE plpgsql                        -- 言語を指定
AS $$                                   -- $$ までの間が関数定義
BEGIN
  RETURN x + y;                         -- x と y の和を返す
END
$$;                                     -- 関数定義の終わり

関数は下記の様にして呼び出すことができます。

SELECT add_func(3, 5);                  -- 関数を呼び出し

関数の一覧を表示する(\df)

\df

関数の内容を表示する(\sf)

\sf add_func

関数の内容を編集する(\ef)

\ef add_func

関数を削除する(DROP FUNCTION)

DROP FUNCTION add_func;

関数内で変数の値を表示する(RAISE)

デバッグなどの目的で、関数の中の変数の値を表示したいときは RAISE を使用します。レベルには DEBUG, LOG, INFO, NOTICE, WARNING, EXCEPTION があります。

RAISE NOTICE 'xx=%, yy=%, zz=%', xx, yy, zz;

トリガー

トリガーを作成する(CREATE TRIGGER)

テーブルにデータが INSERT されたなどのタイミングで関数を呼び出すこともできます。下記の例では x と y に値が INSERT されたら、その合計を z に入れる例を示しています。

CREATE TABLE numbers (id serial, x int, y int, z int);

CREATE FUNCTION add_func()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
  xx integer;
  yy integer;
  zz integer;
BEGIN
  xx := NEW.x;
  yy := NEW.y;
  zz := xx + yy;
  UPDATE numbers SET z = zz WHERE id = NEW.id;
  RETURN NEW;
END
$$;

CREATE TRIGGER add_func_trigger AFTER INSERT ON numbers FOR EACH ROW EXECUTE FUNCTION add_func();
INSERT INTO numbers (x, y) VALUES (5, 3);
SELECT * FROM numbers;

トリガーの一覧を表示する

SELECT * FROM pg_trigger;
SELECT * FROM information_schema.triggers;

トリガーを削除する(DROP TRIGGER)

DROP TRIGGER add_func_trigger ON numbers;

クエリーバッファ

クエリーバッファは直前に実行した SELECT などのコマンドです。クエリーバッファを表示したり、外部エディタで編集して再実行することができます。

クエリーバッファを表示する(\p)

\p

クエリーバッファを編集する(\e)

\e

ヒストリを表示する(\s)

\s

表示モード

詳細表示(\x)

\x を実行すると SELECT の結果が表形式ではなく、レコード毎表示に切り替わります。もう一度 \x を実行すると表形式モードに戻ります。

SELECT * FROM users;
  id  |  name
------+--------
 U001 | Yamada
\x
SELECT * FROM users;
-[ RECORD 1 ]
id   | U001
name | Yamada

\x auto としておくと、表示する内容によって 表形式・レコード形式 を自動的に切り替えてくれます。

\x auto

ヘッダ表示抑制(\t)

\t を実行すると SELECT の結果でヘッダの表示を抑制します。もう一度 \t を実行するとヘッダ表示モードに戻ります。

\t
SELECT * FROM users;
 U001 | Yamada

アライメント制御(\a)

\a を実行すると SELECT の結果でカラムの一番長い文字数に合わせてアライメントしなくなります。もう一度 \a を実行するとアライメントモードに戻ります。

\a
SELECT * FROM users;
id|name
U001|Yamada

カンマ表示(fieldsep)

\a でアライメント無しモードにして \pset fieldsep をカンマ(,)に指定すると、カラムをカンマ(,)区切りで表示することができます。

\a
\pset fieldsep ','
SELECT * FROM users;
id,name
U001,Tanaka

ページャ表示(pager)

レコード数が多いと SELECT の結果が --More-- でページャ表示されますが、下記でページャ表示の有無を切り替えることができます。

\pset pager off
\pset pager on

HTMLモード(\H)

\H を実行すると SELECT の結果を HTMLモードで表示します。もう一度 \H を実行すると通常モードに戻ります。

\H
SELECT * FROM users;
<table border="1">
  <tr>
    <th align="center">id</th>
    <th align="center">name</th>
      :

変数

変数の一覧を表示する(\set)

\set

変数を設定する(\set)

\set USER_ID 'U001'

変数を表示する(\echo)

\echo :USER_ID

変数を使用する(:NAME)

数値として参照したいときは :USER_ID、文字列として参照したいときは :'USER_ID' とします。

SELECT * FROM users WHERE id = :'USER_ID';

その他

外部ファイルを読み込む(\i)

\i file

ヘルプ(\h, \?)

\h で SELECT などのコマンドのヘルプ、\? で \dt などのバックスラッシュコマンドのヘルプを表示することができます。

\h               -- コマンド一覧
\h SELECT        -- コマンドのヘルプ
\?               -- バックスラッシュコマンドの一覧
\? options       -- psqlコマンドオプションの一覧
\? variables     --

初期設定ファイル(.psqlrc)

~/.psqlrc ファイルにコマンドを記述しておくと、ログイン時に自動実行してくれます。

$ cat > ~/.psqlrc <<EOF
SET search_path TO mychema
\pset pager off
EOF

自動インクリメント(SERIAL)

serial 型のカラムは INSERT する度に自動インクリメントされるシーケンス番号を保持することができます。

CREATE TABLE users (id serial PRIMARY KEY, name text);
INSERT INTO users (name) VALUES ('Yamada');
INSERT INTO users (name) VALUES ('Tanaka');
SELECT * FROM users;
 id |  name
----+--------
  1 | Yamada
  2 | Tanaka
(2 rows)

下記の様にしてシーケンス番号を確認したり変更したりできます。

SELECT currval(pg_catalog.pg_get_serial_sequence('users', 'id'));
SELECT * FROM users_id_seq;
SELECT SETVAL('users_id_seq', 100, true);

データベースをダンプする(pg_dump)

pg_dump でデータベースの内容を SQL にダンプすることができます。

$ pg_dump -h localhost -U postgres -d postgres > dump.sql
Copyright (C) 2023 杜甫々
初版:2023年12月31日 最終更新:2023年12月31日
https://www.tohoho-web.com/ex/postgresql.html