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 yourtable
(
id int,
name varchar(100),
status varchar(100),
realm_id int);
insert into yourtable values
(1,'cash','denied',123),
(2,'check','closed',123),
(3,'payroll','denied',123),
(4,'cash','pending',456),
(5,'deposit','suspended',456),
(6,'lending','expired',456),
(7,'loan','trial',456),
(8,'crypto','active',456),
(9,'payroll','closed',456);
CREATE TABLE
INSERT 0 9
SELECT
realm_id,
CASE
WHEN EVERY(status in ('closed', 'denied'))
THEN 'inactive'
ELSE 'active' END AS status
FROM yourtable
GROUP BY realm_id
ORDER BY realm_id;
| realm_id | status |
|---|---|
| 123 | inactive |
| 456 | active |
SELECT 2
SELECT
realm_id,
EVERY(status in ('closed', 'denied')) AS inactive
FROM yourtable
GROUP BY realm_id
ORDER BY realm_id;
| realm_id | inactive |
|---|---|
| 123 | t |
| 456 | f |
SELECT 2