我正在使用 PostgreSQL 9.5,并尝试使用批量插入每天插入数百万行:
INSERT INTO "tours" as cst ("adults","country_id", "price")
VALUES (3, 129, 80), (2, 119,120)
on conflict (adults, country_id) do
update set price = EXCLUDED.price, updated_at = now()
where excluded.price < cst.price
RETURNING ID;
如果价格更低,我会更新行。我想添加else
记录未更新价格的条件。像这样的东西:
INSERT INTO "tours" as cst ("adults","country_id", "price")
VALUES (3, 129, 80), (2, 119,120)
on conflict (adults, country_id)
case when excluded.price < cst.price
then
do update set price = EXCLUDED.price, updated_at = now()
else
INSERT INTO "tours_price_logging" (price, created_at)
values (EXCLUDED.price, now()) end;
但我对这种情况有错误:
ERROR: syntax error at or near "case"
我考虑删除条件where excluded.price < cst.price
从插入并将此逻辑移动到trigger
,但稍后我将使用以下值更新行价格tours_price_logging
而且价格可能比当前价格更高。
所以,我想使用 upsert 和批量插入与日志记录。