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

PostgreSQL About 1,876 words

需求

实现insert or update功能,也称upsert

即:记录如果不存在就插入,记录如果存在就更新。

语法

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

do update set

如果遇到id相同,则更新name为新的值,amount自增1update_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)
Views: 931 · Posted: 2023-11-14

————        END        ————

Give me a Star, Thanks:)

https://github.com/fendoudebb/LiteNote

扫描下方二维码关注公众号和小程序↓↓↓

扫描下方二维码关注公众号和小程序↓↓↓


Today On History
Browsing Refresh