Postgres 12

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 testTable(ProductID int, Customer varchar(20), Status varchar(20));

CREATE TABLE


insert into testTable values (1, 'John',  'Not sold')

INSERT 0 1


insert into testTable values (2, 'John',  'Not sold')

INSERT 0 1


insert into testTable values (3, 'John',  'Sold')

INSERT 0 1


insert into testTable values (4, 'Andrew',  'Not sold')

INSERT 0 1


insert into testTable values (5, 'Andrew',  'Not sold')

INSERT 0 1


insert into testTable values (6, 'Brandon',  'Sold')

INSERT 0 1


select 
    t.*, 
    case when sum( (status = 'Sold')::int ) over(partition by customer) >= 1
        then 'Yes'
        else 'No'
    end
from testTable t


productid
customer
status
case


4
Andrew
Not sold
No

5
Andrew
Not sold
No

6
Brandon
Sold
Yes

1
John
Not sold
Yes

2
John
Not sold
Yes

3
John
Sold
Yes



SELECT 6

productid	customer	status	case
4	Andrew	Not sold	No
5	Andrew	Not sold	No
6	Brandon	Sold	Yes
1	John	Not sold	Yes
2	John	Not sold	Yes
3	John	Sold	Yes