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 servers (
server text
, compliance text
, os text
, ref_id text
);
INSERT INTO servers VALUES
('SERVR1', 'PASS' , 'OK' , null)
, ('SERVR2', 'GOOD' , 'BAD' , null)
, ('SERVR3', 'OK' , 'VALID' , null)
, ('SERVR4', 'FAIL' , 'PASS' , null)
, ('SERVR5', 'BAD' , 'ACCEPT', 'TICKET1')
, ('SERVR6', 'NOGOOD', 'FAIL' , 'TICKET2')
;
CREATE TABLE
INSERT 0 6
SELECT *, com_pass AND os_pass OR ref_id IS NOT NULL AS overall_pass
FROM (
SELECT *
, compliance = ANY ('{GOOD,OK,PASS}') AS com_pass
, os = ANY ('{PASS,OK,VALID,ACCEPT}') AS os_pass
FROM servers
) sub;
| server | compliance | os | ref_id | com_pass | os_pass | overall_pass |
|---|---|---|---|---|---|---|
| SERVR1 | PASS | OK | null | t | t | t |
| SERVR2 | GOOD | BAD | null | t | f | f |
| SERVR3 | OK | VALID | null | t | t | t |
| SERVR4 | FAIL | PASS | null | f | t | f |
| SERVR5 | BAD | ACCEPT | TICKET1 | f | t | t |
| SERVR6 | NOGOOD | FAIL | TICKET2 | f | f | t |
SELECT 6
-- If you need test output
SELECT *, CASE WHEN com_pass AND os_pass OR ref_id IS NOT NULL
THEN 'PASS' ELSE 'FAIL' END AS overall_pass
FROM (
SELECT *
, compliance = ANY ('{GOOD,OK,PASS}') AS com_pass
, os = ANY ('{PASS,OK,VALID,ACCEPT}') AS os_pass
FROM servers
) sub;
| server | compliance | os | ref_id | com_pass | os_pass | overall_pass |
|---|---|---|---|---|---|---|
| SERVR1 | PASS | OK | null | t | t | PASS |
| SERVR2 | GOOD | BAD | null | t | f | FAIL |
| SERVR3 | OK | VALID | null | t | t | PASS |
| SERVR4 | FAIL | PASS | null | f | t | FAIL |
| SERVR5 | BAD | ACCEPT | TICKET1 | f | t | PASS |
| SERVR6 | NOGOOD | FAIL | TICKET2 | f | f | PASS |
SELECT 6