sql - Postgres : upsert a row and update a primary key column

Question

假设我的 Postgres 数据库中有两个表:

create table transactions
(
    id bigint primary key,
    doc_id bigint not null,
    -- lots of other columns...
    amount numeric not null
);

-- same columns
create temporary table updated_transactions
(
    id bigint primary key,
    doc_id bigint not null,
    -- lots of other columns...
    amount numeric not null
);

两个表都只有一个主键，没有唯一索引。

我需要使用以下规则将 updated_transactions 中的行更新到 transactions 中:

• transactions 和 updated_transactions 中的 id 列值不匹配
• doc_id 等其他列(金额 除外)应匹配
• 找到匹配行后，更新 amount 和 id 列
• 当没有找到匹配的行时，插入它

updated_transactions 中的

id 值取自一个序列。业务对象只是填充 updated_transactions 然后合并使用 upsert 查询将新的或更新的行从它转换为 transactions。所以我的旧的未更改交易保持它们的 id 不变，而更新的交易被分配了新的 id。

在 MSSQL 和 Oracle 中，这将是一个类似这样的 merge 语句:

merge into transactions t
using updated_transactions ut on t.doc_id = ut.doc_id, ...
when matched then
    update set t.id = ut.id, t.amount = ut.amount
when not matched then
    insert (t.id, t.doc_id, ..., t.amount)
    values (ut.id, ut.doc_id, ..., ut.amount);

在 PostgreSQL 中，我想它应该是这样的:

insert into transactions(id, doc_id, ..., amount)
select coalesce(t.id, ut.id), ut.doc_id, ... ut.amount
from updated_transactions ut
left join transactions t on t.doc_id = ut.doc_id, ....
    on conflict
    on constraint transactions_pkey
    do update
    set amount = excluded.amount, id = excluded.id

问题出在 do update 子句上:excluded.id 是旧值来自 transactions 表，而我需要来自 updated_transactions 的新值。

ut.id 值对于 do update 子句是不可访问的，我唯一能做的使用的是 excluded 行。但是 excluded 行只有 coalesce(t.id, ut.id)返回现有行的旧 id 值的表达式。

是否可以使用 upsert 查询同时更新 id 和 amount 列？

Answer 1

在用作键的那些列上创建唯一索引，并在 upsert 表达式中传递它的名称，以便它使用它而不是 pkey。然后，如果未找到匹配项，它将使用 updated_transactions 中的 ID 插入行。如果找到匹配项，则您可以使用 excluded.id 从 updated_transactions 获取 ID。

我认为 left join transactions 是多余的。

所以它看起来有点像这样:

insert into transactions(id, doc_id, ..., amount)
select ut.id, ut.doc_id, ... ut.amount
from updated_transactions ut
    on conflict
    on constraint transactions_multi_column_unique_index
    do update
    set amount = excluded.amount, id = excluded.id