「完全攻略 PostgreSQL: 現場で使える最強データベース入門 」という書籍をベースに、自分なりに気になったことやよく使いそうな機能をまとめていきます。
作業リポジトリはこちら:
- 【PostgreSQL 総復習】1. psqlコマンドライン操作
- 【PostgreSQL 総復習】2. データベース・スキーマ・ユーザー
- 【PostgreSQL 総復習】3. データ型・CREATE TABLE
- 【PostgreSQL 総復習】4. SELECT・ウィンドウ関数・CTE (WITH句)
- 【PostgreSQL 総復習】5. INSERT・UPDATE・DELETE・RETURNING
- 【PostgreSQL 総復習】6. トランザクションとACID特性
- 【PostgreSQL 総復習】7. インデックス
INSERT / UPDATE / DELETE の基本操作
INSERT
基本構文
INSERT INTO テーブル名 (col1, col2, ...) VALUES (v1, v2, ...), (...);
サンプル
INSERT INTO users (email, name, age, gender)
VALUES
('ryohei.shibata@example.com', '柴田亮平', 25, 'male'),
('kenta.takahashi@example.com', '高橋健太', 25, 'male');
INSERT ... SELECT
SELECT文の結果を別テーブルに追加することも可能
INSERT INTO employees_backup (id, name, sarary)
SELECT id, name, salarh
FROM employees
WHERE hire_date < '2024-01-01'
ON CONFLICT
ON CONFLICT を指定すると、一意制約または排他制約のエラーは発生させる代わりに、動作を指定することができます。
基本構文
ON CONFLICT (カラム名, ...) DO NOTHING
ON CONFLICT (カラム名, ...) DO UPDATE SET カラム名 = 値, ...
-- emailが重複したらINSERTしない
INSERT INTO users (email, name, age, gender)
VALUES ('ryohei.shibata@example.com', '柴田亮平', 25, 'male')
ON CONFLICT (email) DO NOTHING;
-- emailが重複したら挿入しようとしていた値でアップデート
-- NOTE: 挿入しようとしていた値には特別なEXCLUDEDテーブルでアクセスできる
INSERT INTO users (email, name, age, gender)
VALUES ('ryohei.shibata@example.com', '柴田亮平', 25, 'male')
ON CONFLICT (email) DO UPDATE
SET name = EXCLUDED.name,
age = EXCLUDED.age,
gender = EXCLUDED.gender
UPDATE
基本構文
UPDATE テーブル名 SET col1=v1, col2=2, ... WHERE 条件;
サンプル
UPDATE users
SET name='高橋賢太', age=35
WHERE email = 'kenta.takahashi@example.com';
UPDATE ... FROM
他のテーブルを参照して更新することも可能
-- 確認
SELECT id, total_amount FROM orders WHERE id = '608a0c10-cfdc-4371-a14b-b6cf291804e5';
-- id | total_amount
-- --------------------------------------+--------------
-- 608a0c10-cfdc-4371-a14b-b6cf291804e5 | 124800.00
-- 商品の値段と割引率から注文の合計金額を計算する
UPDATE orders o
SET
total_amount = r.total_amount
FROM (
SELECT
o1.id AS order_id,
SUM(p1.price * (100 - COALESCE(p1.discount, 0)) / 100) AS total_amount
FROM orders o1
JOIN order_items oi1 ON oi1.order_id = o1.id
JOIN products p1 ON p1.id = oi1.product_id
GROUP BY o1.id
) r
WHERE o.id = '608a0c10-cfdc-4371-a14b-b6cf291804e5';
-- 確認
SELECT id, total_amount FROM orders WHERE id = '608a0c10-cfdc-4371-a14b-b6cf291804e5';
-- id | total_amount
-- --------------------------------------+--------------
-- 608a0c10-cfdc-4371-a14b-b6cf291804e5 | 265668.00
DELETE
基本構文
DELETE FROM テーブル名 WHERE 条件;
サンプル
DELETE FROM users WHERE email IN ('ryohei.shibata@example.com' , 'kenta.takahashi@example.com');
全件削除
TRUNCATRE TABLE users;
RETURNING
RETURNING句はPostgreSQLの独自機能で、 INSERT / UPDATE / DELETE 実行後に変更された行を取得できます。
INSERT INTO users (email, name, age, gender)
VALUES
('ryohei.shibata@example.com', '柴田亮平', 25, 'male'),
('kenta.takahashi@example.com', '高橋健太', 25, 'male')
RETURNING id, email, name;
-- id | email | name
-- ----+-----------------------------+----------
-- 54 | ryohei.shibata@example.com | 柴田亮平
-- 55 | kenta.takahashi@example.com | 高橋健太
UPDATE orders o
SET
total_amount = r.total_amount
FROM (
SELECT
o1.id AS order_id,
SUM(p1.price * (100 - COALESCE(p1.discount, 0)) / 100) AS total_amount
FROM orders o1
JOIN order_items oi1 ON oi1.order_id = o1.id
JOIN products p1 ON p1.id = oi1.product_id
GROUP BY o1.id
) r
WHERE o.id = '608a0c10-cfdc-4371-a14b-b6cf291804e5'
RETURNING o.id, o.total_amount;
-- id | total_amount
-- --------------------------------------+--------------
-- 608a0c10-cfdc-4371-a14b-b6cf291804e5 | 265668.00
DELETE FROM users WHERE email IN ('ryohei.shibata@example.com' , 'kenta.takahashi@example.com')
RETURNING id, email;
-- id | email
-- ----+-----------------------------
-- 54 | ryohei.shibata@example.com
-- 55 | kenta.takahashi@example.com