PostgreSQL 小抄 —— 更新與插入

經常要在 PostgreSQL 的表之間搬運數據，但經常忘記常用的語句模式。寫個小抄來作備忘。

根據一個查詢結果更新一張表

UPDATE dummy
SET customer=subquery.customer,
    address=subquery.address,
    partn=subquery.partn
FROM (SELECT address_id, customer, address, partn
      FROM  /* big hairy SQL */ ...) AS subquery
WHERE dummy.address_id=subquery.address_id;

用 subquery 的查詢結果中的某些列更新 dummy，並通過 where 來限定更新範圍。

根據一個查詢結果插入新的數據

INSERT INTO res_id(r_platform, r_id, r_type)
SELECT urls.l_platform AS r_platform,
       urls.l_id AS r_id,
       urls.l_type AS r_type
FROM res_urls urls
LEFT JOIN res_id ids
	ON ids.r_id = urls.l_id
	AND ids.r_platform = urls.l_platform
	AND ids.r_type = urls.l_type
WHERE ids.id IS NULL;

我通常是在數據同步的時候需要這樣更新和插入，在系統還在迭代中的時候，將就的模塊的表同步到新的模塊裡時就很有用。