Is it possible to check the condition inside ON CONFLICT DO UPDATE?

Question

PostgreSQL

The table has an id and date and price field.
Is it possible to somehow write such an ON CONFLICT DO UPDATE condition so that the price update occurs only if the date in the table is older than the one that arrived?

Answer 1

This is done in the following way:

insert into products values (1, '2021-05-01', 150) 
on conflict(id) do update 
set 
  date = case when excluded.date > products.date then excluded.date else products.date end,
  price = case when excluded.date > products.date then excluded.price else products.price end;

PostgreSQL fiddle

Answer 2

The description of the syntax very eloquently hints at the implementation
DO UPDATE SET ... WHERE condition

melkij=> create temp table foo (item int primary key, d date, price numeric);
CREATE TABLE
melkij=> insert into foo values (1, '2021-07-30', 100) on conflict (item) do update set price = excluded.price, d = excluded.d where excluded.d > foo.d;
INSERT 0 1
melkij=> table foo;
 item |     d      | price 
------+------------+-------
    1 | 2021-07-30 |   100
(1 строка)

melkij=> insert into foo values (1, '2021-08-01', 110) on conflict (item) do update set price = excluded.price, d = excluded.d where excluded.d > foo.d;
INSERT 0 1
melkij=> table foo;
 item |     d      | price 
------+------------+-------
    1 | 2021-08-01 |   110
(1 строка)

melkij=> insert into foo values (1, '2021-07-20', 80) on conflict (item) do update set price = excluded.price, d = excluded.d where excluded.d > foo.d;
INSERT 0 0
melkij=> table foo;
 item |     d      | price 
------+------------+-------
    1 | 2021-08-01 |   110
(1 строка)

Answer 3

This is done in a trigger.

Answer 4

Simple enough, but there are nuances.
SELECT MAX(id) FROM random stupidly gets the maximum id in the table (let's denote it as $max).
RAND() * $max gives us some arbitrary ID from the table.
The complex bracket construction with which the random table is joined creates a temporary table, which, roughly speaking, contains a given number of arbitrarily selected (using rand() * $max) IDs.
A condition like r1.id >= r2.id allows you to take into account potential gaps in the ID (some of the entries were deleted, for example).
Now the nuances:
1. I'm not sure that all databases grab a join by a non-integer ID. It might make sense to round first.
2. At first glance, there is no control over duplicates.