代码之家 › 专栏 › 技术社区 › Daryn

Postgres处理约束冲突,然后插入排除的行

postgresql

Daryn · 技术社区 · 4 年前

MyTable
id    date           value    replaced_by_id
1     2020-01-01      10        
2     2020-01-02      20        3
3     2020-01-02      21

在日期上有一个唯一的限制,并用例如

CREATE UNIQUE INDEX my_table_null_test ON my_table (date, (replaced_by_id is null))
WHERE replaced_by_id IS NULL;

沿着

insert into my_table (id, date, value) values (gen_id(), '2020-01-02', 21)
on conflict (date, (replaced_by_id is null) ) where replaced_by_id is null 
do update
set replaced_by_id = excluded.id 

**now insert the new row (insert the excluded row)**

例如,文件的值来自一个文件,该文件中的同一日期有多个值。例如

   date           value
   2020-01-01      10        
   2020-01-02      20     
   2020-01-02      21        
   2020-01-02      22    
   2020-01-02      23    
   2020-01-02      24    
   2020-01-02      22

会导致

MyTable
id    date           value    replaced_by_id
1     2020-01-01      10        
2     2020-01-02      20        3
3     2020-01-02      21        4
4     2020-01-02      22        5
5     2020-01-02      23        6
6     2020-01-02      24        7
7     2020-01-02      22

0 回复 | 直到 4 年前

Akhilesh Mishra 4 年前

我想你不能用 UPSERT 因为这个要求 UPSERT公司 将只对同一个表或 DO Nothing .

原因是:

您添加了 unique index replace_by_id 字段。
您试图在插入之前获取ID以更新现有记录,这是不可能的。

-你必须使用 2 triggers 在您的表上,一个在插入之前,一个在插入之后,如下所示:

插入触发器之前

触发函数

CREATE FUNCTION trig_beforeinsert()
    RETURNS trigger
    LANGUAGE 'plpgsql'
AS $BODY$
declare 
flg int :=0;
begin
flg=(select count(*) from my_table where date_=new.date_ and replaced_by_id is null);

if flg>0 then
new.replaced_by_id=0;
end if;
return new;
end;

$BODY$;

桌上触发器

CREATE TRIGGER my_table_before_insert
    BEFORE INSERT
    ON my_table
    FOR EACH ROW
    EXECUTE PROCEDURE trig_beforeinsert();

触发函数

CREATE FUNCTION trig_afterinsert()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$
declare 
flg int :=0;
begin

if new.replaced_by_id = 0 then
UPDATE my_table set replaced_by_id=new.id where date_=new.date_ and replaced_by_id is null;
UPDATE my_table set replaced_by_id=null where id=new.id;
end if;
return new;
end;

$BODY$;

桌上触发器

CREATE TRIGGER my_table_after_insert
    AFTER INSERT
    ON public.my_table
    FOR EACH ROW
    EXECUTE PROCEDURE public.trig_afterinsert();

DEMO

推荐文章

Johnny T · 基于当前值的SQL合并表[重复]

4 月前

Kallydi · jdbc无法连接到docker中的postgresql数据库

5 月前

dietzi96 · Pandas DataFrame.to_sql随机和静默地失败,没有错误消息

5 月前

Peter Schofield · 类型转换Postgresql

5 月前

BarÄ±Å UÅaklÄ± · 如何在ON冲突更新中使用CTE中的值

5 月前

Kevin Smeeks · Pyspark JDBC分区读取

5 月前

Micah Armantrout · 如何使用sql脚本将数据插入到代码优先的数据库中?

5 月前

Vanortton · 使用Docker开始应用程序开发还是在开发过程中配置Docker?[关闭]

5 月前

Andrus · 如何在sql中查找第二个匹配项

5 月前

MWood · Postgres jsonpath用于引用单例元素或元素数组

5 月前