By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create table tablex (sellerid int , productid int, soldoutdate date);
create unique index ux_indx on tablex(sellerid, productid, coalesce(soldoutdate,'1990-01-01'));
insert into tablex values (1,1,now());
CREATE TABLE
CREATE INDEX
INSERT 0 1
select * from tablex;
| sellerid | productid | soldoutdate |
|---|---|---|
| 1 | 1 | 2023-12-08 |
SELECT 1
INSERT INTO tablex
VALUES (1, 1, NULL)
ON CONFLICT DO NOTHING;
INSERT 0 1
select * from tablex;
| sellerid | productid | soldoutdate |
|---|---|---|
| 1 | 1 | 2023-12-08 |
| 1 | 1 | null |
SELECT 2
INSERT INTO tablex
VALUES (1, 1, NULL)
ON CONFLICT DO NOTHING;
INSERT 0 0
select * from tablex;
| sellerid | productid | soldoutdate |
|---|---|---|
| 1 | 1 | 2023-12-08 |
| 1 | 1 | null |
SELECT 2
INSERT INTO tablex
VALUES (1, 1, NULL)
ON CONFLICT (sellerid, productid, coalesce(soldoutdate,'1990-01-01')) DO UPDATE set soldoutdate = '2020-01-10';
INSERT 0 1
select * from tablex;
| sellerid | productid | soldoutdate |
|---|---|---|
| 1 | 1 | 2023-12-08 |
| 1 | 1 | 2020-01-10 |
SELECT 2