Postgres 11

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 sales (
  id serial
, saleprice numeric
, saledate timestamp
, status text
);

INSERT INTO sales (saleprice, saledate)
VALUES
  (123, '2012-09-28 02:31')
, (123, '2012-09-28 02:31')
, (666, '2012-09-28 02:31')  -- unique
, (123, '2012-09-29 06:36')  -- unique
, (125, '2012-09-29 02:31')
, (125, '2012-09-29 02:31');

CREATE TABLE



INSERT 0 6


UPDATE sales s
SET    status = 'ACTIVE'
WHERE  NOT EXISTS (
   SELECT 1
   FROM   sales s1
   WHERE  s1.saleprice = s.saleprice
   AND    s1.saledate = s.saledate   -- no dupe
   AND    s1.id <> s.id              -- except for row itself
   );
   

UPDATE 2


TABLE sales;


id
saleprice
saledate
status


1
123
2012-09-28 02:31:00
null

2
123
2012-09-28 02:31:00
null

5
125
2012-09-29 02:31:00
null

6
125
2012-09-29 02:31:00
null

3
666
2012-09-28 02:31:00
ACTIVE

4
123
2012-09-29 06:36:00
ACTIVE



SELECT 6

id	saleprice	saledate	status
1	123	2012-09-28 02:31:00	null
2	123	2012-09-28 02:31:00	null
5	125	2012-09-29 02:31:00	null
6	125	2012-09-29 02:31:00	null
3	666	2012-09-28 02:31:00	ACTIVE
4	123	2012-09-29 06:36:00	ACTIVE