在Upsert上发生冲突时添加特定更新规则

Question

你好，我想使用upsert进行循环更新。假设我现在有几个类似这样的数据:下面是表A的位置，我想将此数据插入到表B中：

ref  |id     |phase      |deadlines |realized  |delay |status     |cut_per   |
-----|-------|-----------|----------|----------|------|-----------|----------|
16   | 1     |          6|2019-10-16|2019-11-27|  42.0|DONE       |2021-03-19|
21   | 2     |          1|2019-11-17|NULL      | 488.0|ONGOING    |2021-03-19|
32   | 3     |          3|2019-12-17|NULL      | 489.0|ONGOING    |2021-03-19|

而在其他cut_per中，表A上的数据发生了变化，因此我需要更新表B来跟踪数据：

ref  |id     |phase      |deadlines |realized  |delay |status     |cut_per   |
-----|-------|-----------|----------|----------|------|-----------|----------|
16   | 1     |          6|2019-10-16|2019-11-27|  42.0|DONE       |2021-03-20|
21   | 2     |          1|2019-11-17|2021-03-20| 489.0|ONGOING    |2021-03-20|
32   | 3     |          3|2019-12-17|NULL      | 490.0|ONGOING    |2021-03-20|

由于我使用了这个查询：

select
    concat (id, phase) as ref,
id,
    phase,
    deadlines,
    realized,
    case
        when realized is null then (extract(day
    from
        (now()-deadlines)))
        else extract(day
    from
        (realized - deadlines)) end DELAY,
    status,
    now()::date as cut_per
    from
        table

这是非常详尽和有风险的，因为有可能增加数据大小，并在任何时候通过读取每个cut_per的整个表来使用这个查询，这可能会对我的系统造成潜在的危害。所以我想知道我能不能

select
    concat (id, phase) as ref,
id,
    phase,
    deadlines,
    realized,
    case
        when realized is null then (extract(day
    from
        (now()-deadlines)))
        else extract(day
    from
        (realized - deadlines)) end DELAY,
    status,
    now()::date as cut_per
    from
        table A
  on conflict (ref): set <i want to set delay and status where existing status from existing to new is ONGOING to ONGOING or ONGOING to DONE>

Answer 1

如果您只想更新，则使用更新。我在你的问题中看不到插入的要求：

update table_b b
   set realized = a.realized, 
       delay = coalesce(a.realized, current_date) - a.deadlines, --<< not sure about this 
       cut_per = current_date
from table_a a
where a.id = b.id
  and a.phase = b.phase
  and a.status = b.status --<< not sure about this
  and a.status in ('ONGOING', 'DONE')
  and b.realized IS NULL

如果您想在table_b中保留原始的realized值，那么只需删除该赋值即可。

您希望在(id, phase)上甚至在(id, phase, status)上建立索引来提高效率。