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 t1 (name varchar(10), amount varchar(5), new varchar(10));
insert into t1(name, amount) values ('Harry', '3'), ('Harry', '50'), ('Ron', '5');
3 rows affected

update t1
set new = case 
	when min_amount <= 10 and max_amount > 10 
	then 'pass'
	else 'not pass'
end
from (
	select name, min(amount::numeric) min_amount, max(amount::numeric) max_amount
	from t1 
	group by name 
) t2
where t1.name = t2.name
3 rows affected

select * from t1


name
amount
new


Harry
3
pass

Harry
50
pass

Ron
5
not pass