PostgreSQL: PostgreSQLのINSERT、UPDATE、DELETE操作

最終更新：2026-08-26

データ変更（DML）は日々最も頻繁に行われる操作です。標準SQLに加えて、PostgreSQLはRETURNINGとUPSERTという2つの特長機能を提供します。

1. 学習内容

INSERT INTO: 単一行 / 複数行 / クエリからの挿入
RETURNING句（PG機能: 影響を受けた行を返す）
RETURNING付きのUPDATE / DELETE
UPSERT: INSERT ON CONFLICT（PG機能）
TRUNCATE TABLEによるテーブル全件削除
トランザクション内でのDML（BEGIN / COMMIT / ROLLBACK）

2. 運用担当者の実話

(1) 課題: 商品の一括インポート時の重複データ

CharlieはEコマースデータベースに5000件の商品レコードを一括インポートする必要がありますが、一部の商品は既に存在しています（nameで識別）。彼は以下のことを行う必要があります:

既存商品: 価格と在庫を更新（エラーを発生させずに）
新規商品: 通常通り挿入
インポート後: どの行が新規挿入で、どの行が更新されたかをすぐに把握

(2) 解決策: UPSERT + RETURNING

PostgreSQLのINSERT ON CONFLICT（UPSERT）は、1つのSQL文で挿入と更新の両方を処理し、RETURNING句が影響を受けた行を返します:

SQL

-- Upsert: 新規商品を挿入、既存商品を更新
INSERT INTO products (name, price, stock, category)
VALUES ('Running Shoes', 99.99, 200, 'Footwear')
ON CONFLICT (name)
DO UPDATE SET
    price = EXCLUDED.price,
    stock = products.stock + EXCLUDED.stock
RETURNING id, name, price, stock,
    CASE WHEN xmax = 0 THEN 'inserted' ELSE 'updated' END AS operation;

(3) 成果

INSERTかUPDATEかを判断するための事前SELECTが不要（1クエリ削減）
並行実行時の競合状態が発生しない（アトミック操作）
RETURNINGが即座に結果を返すため、2回目のクエリが不要
一括インポート効率が10倍以上向上

3. INSERT

(1) 基本構文

SQL

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
RETURNING * | column_list;

▶ サンプル: 単一行の挿入

SQL

-- 単一行を挿入
INSERT INTO users (email, name, password_hash)
VALUES ('charlie@example.com', 'Charlie', '$2a$12$hash123');

-- RETURNING付きで挿入（自動生成されたidを取得）
INSERT INTO users (email, name, password_hash)
VALUES ('diana@example.com', 'Diana', '$2a$12$hash456')
RETURNING id, email, created_at;

TEXT 📖 参照専用

 id |       email          |          created_at
----+----------------------+-------------------------------
  5 | diana@example.com    | 2026-07-13 10:30:00+00

▶ サンプル: 複数行の挿入

SQL

-- 1文で複数行を挿入（複数回のINSERTより高速）
INSERT INTO products (name, price, stock, category) VALUES
    ('Wireless Mouse', 29.99, 500, 'Accessories'),
    ('USB-C Cable', 12.99, 1000, 'Accessories'),
    ('Mechanical Keyboard', 79.99, 200, 'Input Devices'),
    ('Monitor Stand', 49.99, 150, 'Accessories'),
    ('Webcam HD', 59.99, 300, 'Video');

TEXT 📖 参照専用

INSERT 0 1

▶ サンプル: クエリからの挿入

SQL

-- アーカイブテーブルを作成し、元のテーブルからデータをコピー
CREATE TABLE orders_archive (LIKE orders INCLUDING ALL);

-- SELECTクエリから行を挿入
INSERT INTO orders_archive
SELECT * FROM orders
WHERE status = 'cancelled'
  AND created_at < NOW() - INTERVAL '90 days';

TEXT 📖 参照専用

INSERT 0 1

4. RETURNING句（PG機能）

RETURNINGはPostgreSQL専用の機能で、INSERT / UPDATE / DELETEの後、影響を受けた行を追加のSELECTなしで直接返します。

(1) 操作別のRETURNING

操作	構文	返される値
INSERT	`INSERT ... RETURNING *`	新しく挿入された行
UPDATE	`UPDATE ... RETURNING *`	更新された行
DELETE	`DELETE ... RETURNING *`	削除された行

比較	MySQL	PostgreSQL
INSERT後のID取得	`SELECT LAST_INSERT_ID()`	`INSERT ... RETURNING id`
削除行の取得	先にSELECT、その後DELETE	`DELETE ... RETURNING *`
UPDATE後の値取得	先にUPDATE、その後SELECT	`UPDATE ... RETURNING *`

▶ サンプル: RETURNINGの魔法

SQL

-- INSERT: 自動生成された値を即��に取得
INSERT INTO users (email, name, password_hash)
VALUES ('eve@example.com', 'Eve', '$2a$12$hash789')
RETURNING id, email, created_at;

-- UPDATE: 変更前後の値を確認
UPDATE products
SET price = 69.99, stock = stock - 10
WHERE id = 1
RETURNING id, name,
    price AS new_price,
    stock AS new_stock;

-- DELETE: 削除前にアーカイブ
WITH deleted AS (
    DELETE FROM orders
    WHERE status = 'cancelled'
      AND created_at < NOW() - INTERVAL '1 year'
    RETURNING *
)
INSERT INTO orders_archive SELECT * FROM deleted;

TEXT 📖 参照専用

INSERT 0 1

5. UPDATE

(1) 基本構文

SQL

UPDATE table_name
SET column1 = value1, column2 = value2, ...
[WHERE condition]
[RETURNING * | column_list];

▶ サンプル: 基本的な更新

SQL

-- 単一行を更新
UPDATE users
SET name = 'Alice Smith', updated_at = NOW()
WHERE email = 'alice@example.com';

-- 条件付き更新
UPDATE products
SET price = price * 0.9  -- 10%割引
WHERE category = 'Accessories' AND stock > 100;

-- RETURNING付き更新
UPDATE products
SET stock = stock - 1
WHERE id = 1 AND stock > 0
RETURNING id, name, stock;

TEXT 📖 参照専用

-- SQL statement executed successfully

▶ サンプル: JOINに基づくUPDATE

SQL

-- 注文明細に基づいて注文合計を更新
UPDATE orders o
SET total_amount = (
    SELECT SUM(quantity * unit_price)
    FROM order_items oi
    WHERE oi.order_id = o.id
)
WHERE o.status = 'pending';

-- FROM句を使用した更新（PG拡張）
UPDATE orders o
SET total_amount = oi_sum.total
FROM (
    SELECT order_id, SUM(quantity * unit_price) AS total
    FROM order_items
    GROUP BY order_id
) oi_sum
WHERE o.id = oi_sum.order_id
  AND o.status = 'pending';

TEXT 📖 参照専用

  result  
----------
   42.50
(1 row)

⚠️ 注意: WHERE句のないUPDATEはテーブル全体を更新します。これは最も一般的な危険操作です。習慣として、SETより先にWHEREを書くようにしてください。

6. DELETE

(1) 基本構文

SQL

DELETE FROM table_name
[WHERE condition]
[RETURNING * | column_list];

▶ サンプル: 削除操作

SQL

-- 特定の行を削除
DELETE FROM products
WHERE stock = 0 AND is_available = false
RETURNING id, name;

-- サブクエリを使った削除
DELETE FROM orders
WHERE user_id IN (
    SELECT id FROM users WHERE is_active = false
);

-- 全行削除（大規模テーブルでは代わりにTRUNCATEを使用！）
-- DELETE FROM logs;  -- 遅い、各行のWALを生成

TEXT 📖 参照専用

DELETE 2

(2) DELETEとTRUNCATEの比較

観点	DELETE	TRUNCATE
速度	行ごとに削除、遅い	一括削除、非常に高速
トランザクション	ロールバック可能（トランザクション内）	ロールバック可能（トランザクション内）
トリガー	行レベルのトリガーを発火	行レベルのトリガーを発火しない
シーケンスリセット	リセットしない	リセット可能（RESTART IDENTITY）
WHERE	サポート	非サポート（テーブル全体を削除）
ユースケース	一部の行を削除	テーブル全体を削除

▶ サンプル: TRUNCATEでテーブルを空にする

SQL

-- 1つのテーブルをトランケート（高速、ストレージをリセット）
TRUNCATE TABLE logs;

-- 複数テーブルを一度にトランケート
TRUNCATE TABLE order_items, orders;

-- カスケード: 外部キー参照のあるテーブルもトランケート
TRUNCATE TABLE users CASCADE;

-- 自動採番シーケンスをリセット
TRUNCATE TABLE products RESTART IDENTITY;

TEXT 📖 参照専用

-- SQL statement executed successfully

7. UPSERT（INSERT ON CONFLICT）

UPSERTはPostgreSQLの最も実用的な特長機能の1つです。挿入データが既存行と競合した場合、エラーを発生させる代わりに自動的に更新��実行します。

(1) UPSERTフローチャート

graph TB
    START["INSERT 行"] --> CONFLICT{"一意制約に<br/>競合？"}
    CONFLICT -->|"競合なし"| INSERT["新しい行を挿入<br/>RETURN inserted"]
    CONFLICT -->|"競合！"| ACTION{"ON CONFLICT<br/>アクション？"}
    ACTION -->|"DO NOTHING"| SKIP["この行をスキップ<br/>エラーなし、更新なし"]
    ACTION -->|"DO UPDATE"| UPDATE["EXCLUDEDを使用して<br/>既存行を更新"]
    UPDATE --> RET2["更新された行をRETURN"]

(2) 構文

SQL

INSERT INTO table_name (column_list)
VALUES (value_list)
ON CONFLICT (column_name | constraint_name)
DO NOTHING | DO UPDATE SET column = EXCLUDED.column ...
[RETURNING *];

キーワード	説明
`ON CONFLICT (column)`	競合を検出するカラム（UNIQUE制約または主キーが必要）
`ON CONFLICT ON CONSTRAINT name`	特定の制約名を指定
`DO NOTHING`	競合時にその行をスキップ（エラーなし、更新なし）
`DO UPDATE SET ...`	競合時に更新を実行
`EXCLUDED`	元々挿入しようとした値を保持する仮想テーブル

▶ サンプル: DO NOTHING（重複を無視）

SQL

-- メールアドレスが存在しない場合のみ挿入
INSERT INTO users (email, name, password_hash)
VALUES ('alice@example.com', 'Alice', '$2a$12$newhash')
ON CONFLICT (email) DO NOTHING;
-- メールアドレスが既に存在してもエラーにならない

TEXT 📖 参照専用

INSERT 0 1

▶ サンプル: DO UPDATE（既存行を更新）

SQL

-- Upsert: 新規商品を挿入、既存商品の価格/在庫を更新
INSERT INTO products (name, price, stock, category)
VALUES
    ('Running Shoes', 99.99, 200, 'Footwear'),
    ('USB-C Cable', 14.99, 800, 'Accessories'),
    ('New Product', 39.99, 50, 'Gadgets')
ON CONFLICT (name)
DO UPDATE SET
    price = EXCLUDED.price,
    stock = products.stock + EXCLUDED.stock,
    category = EXCLUDED.category
RETURNING id, name, price, stock;

TEXT 📖 参照専用

INSERT 0 1

▶ サンプル: 条件付きUPSERT（特定の場合のみ更新）

SQL

-- 新しい価格が安い場合のみ更新
INSERT INTO products (name, price, stock, category)
VALUES ('Running Shoes', 79.99, 100, 'Footwear')
ON CONFLICT (name)
DO UPDATE SET
    price = EXCLUDED.price,
    stock = EXCLUDED.stock
WHERE EXCLUDED.price < products.price;
-- 新しい価格が現在の価格より安い場合のみ更新

TEXT 📖 参照専用

INSERT 0 1

8. トランザクション内でのDML

(1) 基本的なトランザクション操作

コマンド	説明
`BEGIN` または `START TRANSACTION`	トランザクションを開始
`COMMIT`	トランザクションをコミット（永続化）
`ROLLBACK`	トランザクションをロールバック（全変更を元に戻す）
`SAVEPOINT name`	セーブポイントを作成
`ROLLBACK TO SAVEPOINT name`	セーブポイントまでロールバック

▶ サンプル: バッチ操作を保護するトランザクション

SQL

-- 一括インポートのトランザクションを開始
BEGIN;

-- 注文と注文明細を1つの単位として挿入
INSERT INTO orders (user_id, total_amount, status)
VALUES (1, 0, 'pending')
RETURNING id;

-- 上記でid = 10が返されたと仮定
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES
    (10, 1, 2, 89.99),
    (10, 2, 5, 12.99);

-- 注文合計を更新
UPDATE orders
SET total_amount = (2 * 89.99 + 5 * 12.99)
WHERE id = 10;

-- コミット前に確認
SELECT * FROM orders WHERE id = 10;
SELECT * FROM order_items WHERE order_id = 10;

-- 問題なければコミット
COMMIT;

-- 問題があればすべてロールバック
-- ROLLBACK;

TEXT 📖 参照専用

  result  
----------
   42.50
(1 row)

9. 総合サンプル: 商品の一括インポート

SQL

-- ============================================
-- 総合サンプル: UPSERTによる商品一括インポート
-- Charlieが5000商品をインポート、一部は既に存在
-- ============================================

-- ステップ1: 一時インポートテーブルを作成
CREATE TEMP TABLE import_products (
    name VARCHAR(200) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    stock INTEGER DEFAULT 0,
    category VARCHAR(50)
);

-- ステップ2: インポートデータをロード（サンプル行でシミュレーション）
INSERT INTO import_products (name, price, stock, category) VALUES
    ('Running Shoes', 99.99, 200, 'Footwear'),      -- 既に存在
    ('Laptop Backpack', 59.99, 300, 'Accessories'),   -- 既に存在、価格変更
    ('Smart Water Bottle', 34.99, 500, 'Gadgets'),    -- 新規商品
    ('Wireless Earbuds', 79.99, 400, 'Audio'),        -- 新規商品
    ('Yoga Mat', 24.99, 600, 'Fitness');              -- 新規商品

-- ステップ3: 全インポートデータを1文でUpsert
BEGIN;

INSERT INTO products (name, price, stock, category)
SELECT name, price, stock, category
FROM import_products
ON CONFLICT (name)
DO UPDATE SET
    price = EXCLUDED.price,
    stock = products.stock + EXCLUDED.stock
RETURNING id, name, price, stock;

-- ステップ4: 結果を確認
SELECT id, name, price, stock
FROM products
WHERE name IN ('Running Shoes', 'Laptop Backpack',
               'Smart Water Bottle', 'Wireless Earbuds', 'Yoga Mat')
ORDER BY name;

-- ステップ5: クリーンアップ
DROP TABLE import_products;

COMMIT;

❓ よくある質問

Q INSERT ON CONFLICTとREPLACE INTO（MySQL）の違いは何ですか？

A MySQLのREPLACE INTOは実際にはDELETE + INSERTで、古い行を削除して新しい行を挿入します。これにより自動採番IDが変わり、トリガーが発火し、外部キーが壊れます。PGのON CONFLICT DO UPDATEは真のインプレース更新で、IDは変わらず、DELETEトリガーも発火しません。

Q EXCLUDEDとは何ですか？

A EXCLUDEDはUPSERTでPGが提供する仮想テーブルで、競合により挿入に失敗した元のINSERT値を保持しています。新しい値を参照するために使用し、テーブル内の既存の古い値と区別します。例えば、EXCLUDED.priceは新しい価格、products.priceは古い価格です。

Q 一括挿入の最適な行数は？

A 単一の複数行INSERTでは、100〜1000行が推奨されます。1000行を超えるとSQL解析のパフォーマンス問題が発生する可能性があります。より大きなバッチにはCOPYコマンドを使用します（INSERTより5〜10倍高速、バックアップのレッスンで後述）。

Q UPDATEでWHEREを忘れたらどうなりますか？

A テーブル全体が更新されます。これは最も危険なSQLミスの1つです。保護策: 1) 常にSETより先にWHEREを書く。2) トランザクション内で操作し、最初にSELECTで範囲を確認してからUPDATEする。3) psqlでsql_safe_updates = onを設定する。

Q RETURNINGはCTE内で使用できますか？

A はい！これは非常に強力なPGパターンです。DELETE ... RETURNINGとINSERT ... SELECTを組み合わせてデータ移行を実装します: WITH moved AS (DELETE FROM active WHERE ... RETURNING *) INSERT INTO archive SELECT * FROM moved。

Q TRUNCATEはロールバックできますか？

A はい！トランザクション内でTRUNCATEした後にROLLBACKするとデータが復元されます。これはMySQLとは異なります（MySQLのTRUNCATEはDDLでロールバック不可）。PGのTRUNCATEはトランザクション内で安全です。

📖 まとめ

INSERTは単一行、複数行、クエリからの挿入をサポート。複数行挿入は1行ずつの挿入よりはるかに効率的
RETURNING句（PG機能）により、INSERT/UPDATE/DELETEが影響を受けた行を2回目のクエリなしで直接返せる
UPDATEはJOINベースの更新（FROM句）をサポートし、サブクエリより明確
DELETEは1行ずつ削除（遅い）。TRUNCATEは一括削除（高速）。TRUNCATEはトランザクション内でロールバック可能
UPSERT（INSERT ON CONFLICT）はPGのコア機能: 1つのSQL文で「存在しなければ挿入、存在すれば更新」を処理
EXCLUDED仮想テーブルは新しく挿入された値を参照し、テーブル内の既存値と区別
トランザクション（BEGIN/COMMIT/ROLLBACK）はバッチ操作を保護。エラー時にロールバック可能

📝 練習問題

基本（★）: tagsテーブル（id SERIAL主キー、name VARCHAR(50) UNIQUE）を作成し、5つのタグを挿入し、RETURNINGで各行のidとnameを返してください。
中級（★★）: このレッスンのproductsテーブルに対してUPSERTを実行します。3つの商品行を挿入し、そのうち1つは既存の商品名と重複させます。既存商品は価格を更新し、新規商品は通常挿入します。RETURNINGで操作結果を表示してください。
チャレンジ（★★★）: 1つのトランザクション内で以下を完了します: 1) products_backupテーブル（productsと同じ構造）を作成。2) DELETE ... RETURNING + INSERTを使用して、productsから価格が30未満の商品をproducts_backupに移行。3) 移行結果を確認後、COMMIT。完全なSQLスクリプトを記述してください。

Web-Tutorial 技術チーム

複数の開発者によって共同維持されているプログラミングチュートリアルプラットフォーム。各チュートリアルは専門分野の開発者が執筆・レビューしています。正確で信頼性の高いコンテンツを目指しています — 問題を見つけた場合はお知らせください。

私たちについて