Thread: how to implement add using upsert and trigger?
Hi, all：
when I using upsert and trigger to update other table automatically：
create table stat_detail(itemid bigint not null, value bigint, captime bigint);
create table stat_result(itemid bigint primary key, value bigint, cnt bigint);
create or replace function inertfunc() returns trigger as $$
begin
insert into stat_result(itemid,value,cnt) values(new.itemid, new.value, 1)
on conflict(itemid) do update
set value = excluded.value + new.value, cnt = excluded.cnt +1 where excluded.itemid = new.itemid;
return new;
end;
$$
language plpgsql;
create trigger tri_insert after insert on stat_detail for each row execute function inertfunc();
postgres=# insert into stat_detail values(100,1,1);
INSERT 0 1
postgres=# select * from stat_result ;
itemid | value | cnt
--------+-------+-----
100 | 1 | 1
(1 row)
postgres=# insert into stat_detail values(100,1,1);
INSERT 0 1
postgres=# select * from stat_result ;
itemid | value | cnt
--------+-------+-----
100 | 2 | 2
(1 row)
postgres=# insert into stat_detail values(100,1,1);
INSERT 0 1
postgres=# select * from stat_result ;
itemid | value | cnt
--------+-------+-----
100 | 2 | 2
(1 row)
But I want it is "100 3 3". So how I can do?
yin.zhb@163.com
On Mon, Nov 28, 2022 at 1:37 PM yin.zhb@163.com <yin.zhb@163.com> wrote: > on conflict(itemid) do update > set value = excluded.value + new.value, cnt = excluded.cnt +1 where excluded.itemid = new.itemid; OT, but isn't `where excluded.itemid = new.itemid` redundant, given `on conflict(itemid)`? I'm asking more because I'm not sure, for my own education. Thanks, --DD
em， which is does not matter
yin.zhb@163.com
From: Dominique DevienneDate: 2022-11-28 20:47To: yin.zhb@163.comCC: pgsql-generalSubject: Re: how to implement add using upsert and trigger?On Mon, Nov 28, 2022 at 1:37 PM yin.zhb@163.com <yin.zhb@163.com> wrote:> on conflict(itemid) do update> set value = excluded.value + new.value, cnt = excluded.cnt +1 where excluded.itemid = new.itemid;OT, but isn't `where excluded.itemid = new.itemid` redundant, given`on conflict(itemid)`?I'm asking more because I'm not sure, for my own education. Thanks, --DD
Hi, I think you need:
insert into stat_result(itemid,value,cnt) values(new.itemid, new.value, 1)
on conflict(itemid) do update
set value = stat_result.value + new.value, cnt = stat_result.cnt +1;
excluded.value and new.value is the same value from inserted record, but your need a current value from stat_result.
insert into stat_result(itemid,value,cnt) values(new.itemid, new.value, 1)
on conflict(itemid) do update
set value = stat_result.value + new.value, cnt = stat_result.cnt +1;
excluded.value and new.value is the same value from inserted record, but your need a current value from stat_result.
On 28.11.2022 15:37, yin.zhb@163.com wrote:
body { line-height: 1.5; }body { font-size: 14px; font-family: 微软雅黑; color: rgb(0, 0, 0); line-height: 1.5; } Hi, all：when I using upsert and trigger to update other table automatically：create table stat_detail(itemid bigint not null, value bigint, captime bigint);create table stat_result(itemid bigint primary key, value bigint, cnt bigint);create or replace function inertfunc() returns trigger as $$begininsert into stat_result(itemid,value,cnt) values(new.itemid, new.value, 1)on conflict(itemid) do updateset value = excluded.value + new.value, cnt = excluded.cnt +1 where excluded.itemid = new.itemid;return new;end;$$language plpgsql;create trigger tri_insert after insert on stat_detail for each row execute function inertfunc();postgres=# insert into stat_detail values(100,1,1);INSERT 0 1postgres=# select * from stat_result ;itemid | value | cnt--------+-------+-----100 | 1 | 1(1 row)postgres=# insert into stat_detail values(100,1,1);INSERT 0 1postgres=# select * from stat_result ;itemid | value | cnt--------+-------+-----100 | 2 | 2(1 row)postgres=# insert into stat_detail values(100,1,1);INSERT 0 1postgres=# select * from stat_result ;itemid | value | cnt--------+-------+-----100 | 2 | 2(1 row)But I want it is "100 3 3". So how I can do?
-- Pavel Luzanov Postgres Professional: https://postgrespro.com The Russian Postgres Company
Thank you, this result is what I want
yin.zhb@163.com
From: Pavel LuzanovDate: 2022-11-28 21:26Subject: Re: how to implement add using upsert and trigger?Hi, I think you need:
insert into stat_result(itemid,value,cnt) values(new.itemid, new.value, 1)
on conflict(itemid) do update
set value = stat_result.value + new.value, cnt = stat_result.cnt +1;
excluded.value and new.value is the same value from inserted record, but your need a current value from stat_result.On 28.11.2022 15:37, yin.zhb@163.com wrote:Hi, all：when I using upsert and trigger to update other table automatically：create table stat_detail(itemid bigint not null, value bigint, captime bigint);create table stat_result(itemid bigint primary key, value bigint, cnt bigint);create or replace function inertfunc() returns trigger as $$begininsert into stat_result(itemid,value,cnt) values(new.itemid, new.value, 1)on conflict(itemid) do updateset value = excluded.value + new.value, cnt = excluded.cnt +1 where excluded.itemid = new.itemid;return new;end;$$language plpgsql;create trigger tri_insert after insert on stat_detail for each row execute function inertfunc();postgres=# insert into stat_detail values(100,1,1);INSERT 0 1postgres=# select * from stat_result ;itemid | value | cnt--------+-------+-----100 | 1 | 1(1 row)postgres=# insert into stat_detail values(100,1,1);INSERT 0 1postgres=# select * from stat_result ;itemid | value | cnt--------+-------+-----100 | 2 | 2(1 row)postgres=# insert into stat_detail values(100,1,1);INSERT 0 1postgres=# select * from stat_result ;itemid | value | cnt--------+-------+-----100 | 2 | 2(1 row)But I want it is "100 3 3". So how I can do?-- Pavel Luzanov Postgres Professional: https://postgrespro.com The Russian Postgres Company