PostgreSQL 实现 upsert 插入或者更新功能

需求

实现insert or update功能，也称upsert。

即：记录如果不存在就插入，记录如果存在就更新。

语法

使用insert on conflict实现upsert功能，onflict的字段必须是主键或唯一键。

do update set

如果遇到id相同，则更新name为新的值，amount自增1，update_ts更新为当前时间。

excluded为虚拟的表，表示SQL语句中values里传入的新的值。

insert into product(id, name, amount, update_ts)
values (1, '产品1', 1, current_timestamp)
on conflict(id) do update set
name=excluded.name,
amount=product.amount+1,
update_ts=current_timestamp;

do nothing

如果遇到id相同，不做任何事。

insert into product(id, name, amount, update_ts)
values (1, '产品1', 1, current_timestamp)
on conflict(id) do nothing;

where

如果符合where条件才进行upsert操作。

示例一：

insert into product(id, name, amount, update_ts)
values (1, '产品1', 1, current_timestamp)
on conflict(id) do update set
name=excluded.name,
amount=product.amount+1,
update_ts=current_timestamp
where product.name is distinct from excluded.name or product.amount = 10;

示例二：

insert into product(id, name, amount, update_ts)
values (1, '产品1', 1, current_timestamp)
on conflict(id) do update set
name=excluded.name,
amount=product.amount+1,
update_ts=current_timestamp
where product.name != excluded.name and product.name != '产品2';

原始字段累加

如上述示例amount字段累加，必须使用amount=product.amount+1，其中product是表名，否则会抛出以下错误

Caused by: org.postgresql.util.PSQLException: ERROR: column reference "amount" is ambiguous
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2675)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2365)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:355)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:490)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:408)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:167)
    at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:156)