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());
1 rows affected
select * from tablex;
| sellerid | productid | soldoutdate |
|---|---|---|
| 1 | 1 | 2021-06-17 |
INSERT INTO tablex
VALUES (1, 1, NULL)
ON CONFLICT DO NOTHING;
1 rows affected
select * from tablex;
| sellerid | productid | soldoutdate |
|---|---|---|
| 1 | 1 | 2021-06-17 |
| 1 | 1 | null |
INSERT INTO tablex
VALUES (1, 1, NULL)
ON CONFLICT DO NOTHING;
select * from tablex;
| sellerid | productid | soldoutdate |
|---|---|---|
| 1 | 1 | 2021-06-17 |
| 1 | 1 | null |