Postgres 16

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 regions (
id serial primary key,
name varchar NOT NULL,
capital_id integer NOT NULL
  );

CREATE TABLE capitals (
id serial primary key,
name varchar NOT NULL,
is_visited boolean NOT NULL,
region_id integer NOT NULL references regions(id)
  );

ALTER TABLE regions ADD CONSTRAINT fk_regions_capitals FOREIGN KEY (capital_id) references capitals(id);

CREATE TABLE

CREATE TABLE

ALTER TABLE

INSERT INTO regions (name) VALUES 
  ('Камчатка'),
  ('Чукотка');

INSERT INTO capitals (name, is_visited) VALUES
  ('Петропавловск-Камчатский', TRUE),
  ('Анадырь', FALSE);

ERROR:  null value in column "capital_id" of relation "regions" violates not-null constraint
DETAIL:  Failing row contains (1, Камчатка, null).

SELECT * FROM regions;
SELECT * FROM capitals;

id
name
capital_id

SELECT 0

id
name
is_visited
region_id

SELECT 0