How to find out if an upsert was an update with PostgreSQL 9.5+ UPSERT?

by

I believe xmax::text::int > 0 would be the easiest trick:

so=# DROP TABLE IF EXISTS tab;
NOTICE:  table "tab" does not exist, skipping
DROP TABLE
so=# CREATE TABLE tab(id INT PRIMARY KEY, col text);
CREATE TABLE
so=# INSERT INTO tab(id, col) VALUES (1,'a'), (2, 'b');
INSERT 0 2
so=# INSERT INTO tab(id, col)
VALUES (3, 'c'), (4, 'd'), (1,'aaaa')
ON CONFLICT (id) DO UPDATE SET col = EXCLUDED.col
returning *,case when xmax::text::int > 0 then 'updated' else 'inserted' end,ctid;
 id | col  |   case   | ctid
----+------+----------+-------
  3 | c    | inserted | (0,3)
  4 | d    | inserted | (0,4)
  1 | aaaa | updated  | (0,5)
(3 rows)

INSERT 0 3
so=# INSERT INTO tab(id, col)
VALUES (3, 'c'), (4, 'd'), (1,'aaaa')
ON CONFLICT (id) DO UPDATE SET col = EXCLUDED.col
returning *,case when xmax::text::int > 0 then 'updated' else 'inserted' end,ctid;
 id | col  |  case   | ctid
----+------+---------+-------
  3 | c    | updated | (0,6)
  4 | d    | updated | (0,7)
  1 | aaaa | updated | (0,8)
(3 rows)

INSERT 0 3

More Related Contents:

  1. Use multiple conflict_target in ON CONFLICT clause
  2. Differentiate inserted and updated rows in UPSERT using system columns
  3. PostgreSQL INSERT ON CONFLICT UPDATE (upsert) use all excluded values
  4. Insert, on duplicate update in PostgreSQL?
  5. How to UPSERT (MERGE, INSERT … ON DUPLICATE UPDATE) in PostgreSQL?
  6. Postgres: INSERT if does not exist already
  7. serial in postgres is being increased even though I added on conflict do nothing
  8. Return rows from INSERT with ON CONFLICT without needing to update
  9. What is PostgreSQL equivalent to autoincrement?
  10. How do I specify a password to ‘psql’ non-interactively?
  11. INSERT with dynamic table name in trigger function
  12. psql: FATAL: Ident authentication failed for user “postgres”
  13. What is the format for the PostgreSQL connection string / URL?
  14. Where does PostgreSQL store the database?
  15. Is there a way to disable function overloading in Postgres
  16. Grant all on a specific schema in the db to a group role in PostgreSQL
  17. Can I make a plpgsql function return an integer without using a variable?
  18. Migrate database from Postgres to MySQL [closed]
  19. How can I start PostgreSQL on Windows?
  20. Test for null in function with varying parameters
  21. PostgreSQL: Why psql can’t connect to server?
  22. Postgres error on insert – ERROR: invalid byte sequence for encoding “UTF8”: 0x00
  23. Changing a postgres containers server port in Docker Compose
  24. How do you find the disk size of a Postgres / PostgreSQL table and its indexes
  25. no pg_hba.conf entry for host
  26. PostgreSQL – disabling constraints
  27. PostgreSQL authentication method 10 not supported
  28. Spring Docker container cannot access Postgres Docker container
  29. Format specifier for integer variables in format() for EXECUTE?
  30. PostgreSQL composite primary key

Leave a Comment