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 users (
name varchar,
amount int
);
insert into users values
('dan', -1),
('john', 1),
('cat', 2),
('sam', 3),
('miguel', 4),
('tom', 5),
('kyle', 9)
;
create table bins (
bin int,
label varchar
);
insert into bins values
(NULL, '<0'),
(0, '0-2'),
(2, '2-6'),
(6, '6-7'),
(7, '7+')
;
select u.*, label
from users as u
left join (
select bins.*, lead(bin) over (order by bin nulls first) as next_bin
from bins
) b on
((bin is null or amount >= bin) and (next_bin is null or amount < next_bin))
7 rows affected
5 rows affected
| name | amount | label |
|---|---|---|
| dan | -1 | <0 |
| john | 1 | 0-2 |
| cat | 2 | 2-6 |
| sam | 3 | 2-6 |
| miguel | 4 | 2-6 |
| tom | 5 | 2-6 |
| kyle | 9 | 7+ |