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 analysis_result
(
site_id INT,
buffer_result_id INT
)
alter table analysis_result
add constraint ar_check check (
 (site_id IS NOT NULL OR buffer_result_id IS NOT NULL) 
  AND NOT(site_id IS NOT NULL AND buffer_result_id IS NOT NULL) 
 );
INSERT INTO analysis_result
VALUES (NULL, NULL)
ERROR:  new row for relation "analysis_result" violates check constraint "ar_check"
DETAIL:  Failing row contains (null, null).


INSERT INTO analysis_result
VALUES (1, 2)
ERROR:  new row for relation "analysis_result" violates check constraint "ar_check"
DETAIL:  Failing row contains (1, 2).


INSERT INTO analysis_result
VALUES (NULL, 2)
1 rows affected

INSERT INTO analysis_result
VALUES (1, NULL)
1 rows affected

SELECT * FROM analysis_result


site_id
buffer_result_id


null
2

1
null

site_id	buffer_result_id
null	2
1	null