Question 282170 in PostgreSQL Question posted in
The official documentation can be found here.

Upsert in PostgreSQL: Get number of Updates and Inserts not Affected

when doing an UPSERT statement with postgres, such as

INSERT INTO tab1.summary(
table_name, target_field, check_n)
VALUES ('tab1', 'col1', 10),
('tab2', 'col2', 10)
ON CONFLICT(table_name, target_field, check_n)
DO UPDATE SET check_n = 20;

, returns nothing as data output, however, the message shows INSERT 0 2, which means that two rows are affected without any error.

However, is there any way to retrieve the number of inserted and updates rows separately as data output?

Tags:

2

Answers


  1. 0

    You can use a returning clause: demo

    with cte as (
  INSERT INTO tab1.summary(table_name, target_field, check_n)
  VALUES ('tab1', 'col1', 10),
         ('tab2', 'col2', 10)
  ON CONFLICT(table_name, target_field, check_n)
  DO UPDATE SET check_n = 20
  RETURNING check_n=20 as was_updated)
select count(*)filter(where not was_updated) as inserted_count,
       count(*)filter(where     was_updated) as updated_count from cte;
    inserted_count updated_count
    2 0

    And if you run it again, forcing the conflict:

    inserted_count updated_count
    0 2

    Of course this method only works if your batch doesn’t contain the default check_n=20, in which case it’d assume it was updated, not inserted. This can be worked around (although a bit invasively) by adding a reflector column: demo2

    alter table tab1.summary add column was_updated boolean default false;

with cte as (
  INSERT INTO tab1.summary(table_name, target_field, check_n)
  VALUES ('tab1', 'col1', 10),
         ('tab2', 'col2', 10)
  ON CONFLICT(table_name, target_field, check_n)
  DO UPDATE SET check_n = 20, was_updated=true
  RETURNING was_updated)
select count(*)filter(where not was_updated) as inserted_count,
       count(*)filter(where     was_updated) as updated_count from cte;
    Login or Signup to reply.
  2. 0

    You can observe a hidden system column xmax:

    xmax The identity (transaction ID) of the deleting transaction, or zero for an undeleted row version. It is possible for this column to be nonzero in a visible row version. That usually indicates that the deleting transaction hasn’t committed yet, or that an attempted deletion was rolled back.

    An upsert will show zero xmax for inserted and non-zero for updated rows because under the hood, an update is actually a combination of insert and delete: it inserts a new tuple and deletes the old one, marking as outdated and ready to be removed by vacuum: demo

    with cte as (
  INSERT INTO tab1.summary(table_name, target_field, check_n)
  VALUES ('tab1', 'col1', 10),
         ('tab2', 'col2', 20)
  ON CONFLICT(table_name, target_field, check_n)
  DO UPDATE SET check_n = 20
  RETURNING xmax<>0 as was_updated )
select count(*)filter(where not was_updated) as inserted_count,
       count(*)filter(where     was_updated) as updated_count from cte;

    You can compare that to the reflector column: demo2

    select * from tab1.summary;
    table_name target_field check_n was_updated
    tab1 col1 10 f
    tab2 col2 10 f 
    INSERT INTO tab1.summary(table_name, target_field, check_n)
VALUES ('tab1', 'col1', 10),
       ('tab2', 'col2', 20)
ON CONFLICT(table_name, target_field, check_n)
DO UPDATE SET check_n = 20, was_updated=true
RETURNING was_updated,xmax;
    was_updated xmax
    t 732
    f 0 
    select * from tab1.summary;
    table_name target_field check_n was_updated
    tab2 col2 10 f
    tab1 col1 20 t
    tab2 col2 20 f
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search