代码之家 › 专栏 › 技术社区 › 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

推荐文章

Ishaan Adarsh · 未知的META规范,无法验证。[规范v1.0.1]

1 年前

tggtsed · PostgreSQL从平均值中排除值

1 年前

Dawid · 为什么我不能在子查询中使用表别名?

2 年前

LAZ · ORDER BY id DESC LIMIT 100返回第一行而不是最后一行

2 年前

Mohammed Shaheer · 查找列中的数据是否满足sql中的数据类型条件

2 年前

AKang123. · Postgres:重复行,但更新json中的字段

2 年前

CraZyCoDer · 在PostgreSQL中锁定潜在事务

2 年前

ranebec · 计数时如何返回0而不是null?

2 年前

Ramón J Romero y Vigil · 将没有时区列的postgres时间戳转换为日期

2 年前

Flo · 分组依据中的SQL大小写

2 年前