PostgreSQL SQL入門 - INSERT/UPDATE/DELETEでデータを操作する

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

-- 社員テーブルの作成
CREATE TABLE employees (
    employee_id   SERIAL PRIMARY KEY,
    name          VARCHAR(100) NOT NULL,
    department    VARCHAR(50) NOT NULL,
    salary        INTEGER NOT NULL,
    hire_date     DATE DEFAULT CURRENT_DATE,
    is_active     BOOLEAN DEFAULT TRUE,
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 部署テーブルの作成
CREATE TABLE departments (
    department_id   SERIAL PRIMARY KEY,
    department_name VARCHAR(50) NOT NULL UNIQUE,
    budget          INTEGER DEFAULT 0,
    manager_id      INTEGER
);

-- 監査ログテーブルの作成
CREATE TABLE audit_logs (
    log_id        SERIAL PRIMARY KEY,
    table_name    VARCHAR(50) NOT NULL,
    operation     VARCHAR(10) NOT NULL,
    old_data      JSONB,
    new_data      JSONB,
    executed_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

1
2

INSERT INTO テーブル名 (カラム名1, カラム名2, ...)
VALUES (値1, 値2, ...);

1
2
3

-- カラム名を指定して挿入
INSERT INTO employees (name, department, salary)
VALUES ('田中太郎', '開発部', 450000);

1
2
3

-- カラム名を省略（非推奨）
INSERT INTO employees
VALUES (DEFAULT, '山田花子', '営業部', 380000, DEFAULT, DEFAULT, DEFAULT, DEFAULT);

1
2
3
4
5
6
7
8

-- DEFAULTキーワードを使用
INSERT INTO employees (name, department, salary, hire_date)
VALUES ('佐藤次郎', '人事部', 420000, DEFAULT);

-- 確認
SELECT employee_id, name, department, salary, hire_date
FROM employees
WHERE name = '佐藤次郎';

1
2
3
4
5
6
7

-- 複数行の一括挿入
INSERT INTO employees (name, department, salary)
VALUES
    ('鈴木一郎', '開発部', 520000),
    ('高橋美咲', '開発部', 480000),
    ('伊藤健太', '営業部', 400000),
    ('渡辺真理', '人事部', 390000);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

-- 部署データの挿入
INSERT INTO departments (department_name, budget)
VALUES
    ('開発部', 50000000),
    ('営業部', 30000000),
    ('人事部', 20000000);

-- 開発部の社員を別テーブルにコピーする例
CREATE TABLE dev_team_backup AS SELECT * FROM employees WHERE 1=0;

INSERT INTO dev_team_backup
SELECT * FROM employees
WHERE department = '開発部';

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

-- 部署ごとの人数を集計テーブルに挿入
CREATE TABLE department_summary (
    department VARCHAR(50) PRIMARY KEY,
    employee_count INTEGER,
    total_salary BIGINT,
    summarized_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO department_summary (department, employee_count, total_salary)
SELECT
    department,
    COUNT(*),
    SUM(salary)
FROM employees
GROUP BY department;

1
2
3
4

-- RETURNING句で挿入されたデータを取得
INSERT INTO employees (name, department, salary)
VALUES ('中村優子', '開発部', 460000)
RETURNING employee_id, name, created_at;

1
2
3

INSERT INTO employees (name, department, salary)
VALUES ('小林大輔', '営業部', 410000)
RETURNING *;

1
2
3
4
5
6
7
8

-- 部署テーブルへのUPSERT
INSERT INTO departments (department_name, budget)
VALUES ('開発部', 55000000)
ON CONFLICT (department_name)
DO UPDATE SET budget = EXCLUDED.budget;

-- 確認
SELECT * FROM departments WHERE department_name = '開発部';

1
2
3
4
5

-- DO NOTHING の例
INSERT INTO departments (department_name, budget)
VALUES ('開発部', 60000000)
ON CONFLICT (department_name)
DO NOTHING;

1
2
3

UPDATE テーブル名
SET カラム名1 = 値1, カラム名2 = 値2, ...
WHERE 条件;

1
2
3
4

-- 特定社員の給与を更新
UPDATE employees
SET salary = 500000
WHERE name = '田中太郎';

1
2
3
4
5
6
7

-- 複数カラムの同時更新
UPDATE employees
SET
    department = '企画部',
    salary = 550000,
    updated_at = CURRENT_TIMESTAMP
WHERE employee_id = 1;

1
2
3
4
5
6
7
8
9

-- 全社員の給与を5%アップ
UPDATE employees
SET salary = salary * 1.05
WHERE is_active = TRUE;

-- 特定部署の給与を10000円アップ
UPDATE employees
SET salary = salary + 10000
WHERE department = '開発部';

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

-- 給与が400000未満かつ開発部の社員の給与を400000に引き上げ
UPDATE employees
SET salary = 400000
WHERE salary < 400000
  AND department = '開発部';

-- サブクエリを使った条件指定
UPDATE employees
SET is_active = FALSE
WHERE department IN (
    SELECT department_name
    FROM departments
    WHERE budget < 25000000
);

1
2
3
4
5
6

-- 部署テーブルから予算情報を取得して更新
UPDATE employees e
SET salary = salary + (d.budget / 100)
FROM departments d
WHERE e.department = d.department_name
  AND d.budget > 40000000;

1
2
3
4
5

-- 更新後の値を取得
UPDATE employees
SET salary = salary + 20000
WHERE department = '営業部'
RETURNING employee_id, name, salary;

1
2
3
4
5

-- 更新前後の値を同時に取得（PostgreSQL 18以降）
UPDATE employees
SET salary = salary + 30000
WHERE employee_id = 2
RETURNING OLD.salary AS old_salary, NEW.salary AS new_salary, name;

1
2
3
4
5
6
7
8
9

-- 更新前に対象行を確認する習慣
SELECT employee_id, name, salary
FROM employees
WHERE department = '開発部';

-- 確認後に更新を実行
UPDATE employees
SET salary = salary + 50000
WHERE department = '開発部';

1
2

DELETE FROM テーブル名
WHERE 条件;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- 特定の社員を削除
DELETE FROM employees
WHERE employee_id = 5;

-- 非アクティブな社員を削除
DELETE FROM employees
WHERE is_active = FALSE;

-- 複合条件での削除
DELETE FROM employees
WHERE department = '人事部'
  AND salary < 400000;

1
2
3
4
5

-- 予算が少ない部署の社員を削除
DELETE FROM employees e
USING departments d
WHERE e.department = d.department_name
  AND d.budget < 15000000;

1
2
3
4
5
6
7

-- サブクエリを使った削除（標準SQL準拠）
DELETE FROM employees
WHERE department IN (
    SELECT department_name
    FROM departments
    WHERE budget < 15000000
);

1
2
3
4
5
6
7
8
9

-- 削除されたデータを取得
DELETE FROM employees
WHERE is_active = FALSE
RETURNING *;

-- 特定のカラムのみ取得
DELETE FROM employees
WHERE employee_id = 10
RETURNING employee_id, name, department;

1
2
3
4
5

-- DELETE文で全行削除
DELETE FROM audit_logs;

-- TRUNCATE文で全行削除
TRUNCATE TABLE audit_logs;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- 基本的なTRUNCATE
TRUNCATE TABLE audit_logs;

-- 複数テーブルを同時にTRUNCATE
TRUNCATE TABLE audit_logs, department_summary;

-- 外部キー制約がある場合（参照先も含めて削除）
TRUNCATE TABLE departments CASCADE;

-- シーケンスもリセット
TRUNCATE TABLE employees RESTART IDENTITY;

1
2
3
4

-- 外部キー制約エラーの例
-- departmentsテーブルがemployeesから参照されている場合
DELETE FROM departments WHERE department_name = '開発部';
-- ERROR: update or delete on table "departments" violates foreign key constraint

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

-- トランザクションの基本的な流れ
BEGIN;

-- 複数の操作を実行
UPDATE employees
SET salary = salary - 50000
WHERE employee_id = 1;

UPDATE employees
SET salary = salary + 50000
WHERE employee_id = 2;

-- 問題がなければ確定
COMMIT;

1
2
3
4
5
6
7
8
9

BEGIN;

UPDATE employees
SET salary = salary - 100000
WHERE employee_id = 1;

-- 何らかの理由で取り消したい
ROLLBACK;
-- salary は変更されていない状態に戻る

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

-- 銀行の送金処理
BEGIN;

-- 送金元から引き落とし
UPDATE accounts
SET balance = balance - 50000
WHERE account_id = 'A001'
  AND balance >= 50000;  -- 残高チェック

-- 更新された行数を確認（アプリケーション側で判定）
-- 0行の場合は残高不足

-- 送金先に入金
UPDATE accounts
SET balance = balance + 50000
WHERE account_id = 'B002';

COMMIT;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

BEGIN;

-- 最初の操作
INSERT INTO employees (name, department, salary)
VALUES ('テスト太郎', '開発部', 400000);

-- セーブポイントを設定
SAVEPOINT sp1;

-- 次の操作
INSERT INTO employees (name, department, salary)
VALUES ('テスト次郎', '開発部', 'invalid');  -- エラー発生

-- sp1まで戻る（最初のINSERTは維持）
ROLLBACK TO sp1;

-- 別の操作を実行
INSERT INTO employees (name, department, salary)
VALUES ('テスト次郎', '開発部', 420000);

COMMIT;

1
2
3
4
5

-- 自動コミット（各文が個別にコミット）
INSERT INTO employees (name, department, salary) VALUES ('A', '開発部', 400000);
-- ここで自動コミット
INSERT INTO employees (name, department, salary) VALUES ('B', '開発部', 410000);
-- ここで自動コミット

1
2
3
4
5
6

-- トランザクションブロック（明示的）
BEGIN;
INSERT INTO employees (name, department, salary) VALUES ('C', '開発部', 420000);
INSERT INTO employees (name, department, salary) VALUES ('D', '開発部', 430000);
COMMIT;
-- ここで両方の挿入がコミット

1
2
3
4
5
6

-- 良い例：関連する操作のみをトランザクションに含める
BEGIN;
UPDATE orders SET status = 'shipped' WHERE order_id = 100;
UPDATE inventory SET stock = stock - 1 WHERE product_id = 50;
INSERT INTO shipping_logs (order_id, shipped_at) VALUES (100, CURRENT_TIMESTAMP);
COMMIT;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

BEGIN;

-- 更新前のデータを取得
INSERT INTO audit_logs (table_name, operation, old_data, new_data)
SELECT
    'employees',
    'UPDATE',
    to_jsonb(e.*),
    NULL
FROM employees e
WHERE employee_id = 1;

-- データを更新
UPDATE employees
SET salary = 600000, updated_at = CURRENT_TIMESTAMP
WHERE employee_id = 1;

-- 更新後のデータで監査ログを更新
UPDATE audit_logs
SET new_data = (
    SELECT to_jsonb(e.*)
    FROM employees e
    WHERE employee_id = 1
)
WHERE log_id = (SELECT MAX(log_id) FROM audit_logs);

COMMIT;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

BEGIN;

-- 最初のデータ処理
SAVEPOINT batch_sp;
INSERT INTO employees (name, department, salary) VALUES ('バッチ1', '開発部', 400000);

-- 2番目のデータ処理（エラーを想定）
SAVEPOINT batch_sp;
BEGIN
    INSERT INTO employees (name, department, salary) VALUES ('バッチ2', '開発部', 'error');
EXCEPTION WHEN OTHERS THEN
    ROLLBACK TO batch_sp;
END;

-- 3番目のデータ処理
SAVEPOINT batch_sp;
INSERT INTO employees (name, department, salary) VALUES ('バッチ3', '開発部', 420000);

COMMIT;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

-- UPSERTの結果をログに記録
WITH upsert_result AS (
    INSERT INTO departments (department_name, budget)
    VALUES ('マーケティング部', 25000000)
    ON CONFLICT (department_name)
    DO UPDATE SET budget = EXCLUDED.budget
    RETURNING *, (xmax = 0) AS is_insert
)
INSERT INTO audit_logs (table_name, operation, new_data)
SELECT
    'departments',
    CASE WHEN is_insert THEN 'INSERT' ELSE 'UPDATE' END,
    to_jsonb(upsert_result.*)
FROM upsert_result;