Обсуждение: 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

---

 

From: Dominique Devienne

Date: 2022-11-28 20:47

To: yin.zhb@163.com

CC: pgsql-general

Subject: 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.

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 $$

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

-- 
Pavel Luzanov
Postgres Professional: https://postgrespro.com
The Russian Postgres Company

---

 

From: Pavel Luzanov

Date: 2022-11-28 21:26

To: yin.zhb@163.com; pgsql-general

Subject: 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 $$

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

-- 
Pavel Luzanov
Postgres Professional: https://postgrespro.com
The Russian Postgres Company