【postgres】競合が発生したときに上書き保存するとかしないとか

postgresの更新処理に関する個人的なメモ。

以下のようなテーブルを用意。

CREATE TABLE orders_tmp (
  order_id INT PRIMARY KEY,
  customer_name TEXT,
  order_date TIMESTAMP,
  item_name TEXT,
  price INT
);

ここに以下のようなCSVデータを挿入する。

order_id,customer_name,order_date,item_name,price
1,山田太郎,2022-03-20 10:30:00,商品A,1000
2,佐藤花子,2022-03-21 11:00:00,商品B,2000
3,鈴木次郎,2022-03-22 12:00:00,商品C,3000

インサート文は以下のとおり。

COPY orders_tmp FROM '/path/to/orders.csv' WITH CSV HEADER;

INSERT INTO orders (order_id, customer_name, order_date, item_name, price)
SELECT order_id, customer_name, order_date, item_name, price
FROM orders_tmp
ON CONFLICT (order_id) DO UPDATE
SET customer_name = EXCLUDED.customer_name,
    order_date = EXCLUDED.order_date,
    item_name = EXCLUDED.item_name,
    price = EXCLUDED.price;

DROP TABLE orders_tmp;

ここで注目すべきはON CONFLICTからDROP TABLE前の部分。ここには競合が発生した場合の処理が書かれている。このSQL分の場合、costomer_name, order_date, item_name, priceを新しいデータで更新するように処理している。なお、EXCLUDEには新しく挿入するレコードの情報が入っていると考えればOK.

次に、”ON CONFLICT (order_id) DO UPDATE”とあるが、このorder_idの部分には主キーか、ユニーク制約がかかっているカラム名を指定する。これにより、その指定したカラムで競合が起きた時にどのように更新するかを指定することができる。

ここで疑問に思うのは、もしユニーク制約や主キーでないカラムの場合は無条件に更新されてしまうかということだ。調べたところによると、ON CONFLICT (カラムを指定) DO NOTHINGを指定しない場合、自動で更新されてしまうようだ。以下にその例を示す。

INSERT INTO orders (order_id, customer_name, order_date, item_name, price)
VALUES (1, '山田太郎', '2022-03-20 10:30:00', '商品A', 1000),
       (2, '山田太郎', '2022-03-20 10:30:00', '商品B', 2000)
ON CONFLICT (customer_name, order_date) DO NOTHING;
Copied title and URL