Postgres 9.6

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  (user_id   int PRIMARY KEY);
CREATE TABLE office (office_id int PRIMARY KEY);
CREATE TABLE import (import_id int PRIMARY KEY);

INSERT INTO users  VALUES (1);
INSERT INTO office VALUES (2);
INSERT INTO import VALUES (3);

CREATE TABLE box (
   box_id      serial PRIMARY KEY
 , box_content text -- NOT NULL?
 , from_user   int  REFERENCES users(user_id)
 , from_office int  REFERENCES office(office_id)
 , from_import int  REFERENCES import(import_id)
  -- more?
 , CONSTRAINT exactly_one_from
   CHECK (CASE WHEN from_user   IS NULL THEN 0 ELSE 1 END
        + CASE WHEN from_office IS NULL THEN 0 ELSE 1 END
        + CASE WHEN from_import IS NULL THEN 0 ELSE 1 END = 1)
);
1 rows affected

1 rows affected

1 rows affected

INSERT INTO box VALUES (1,'foo', NULL, NULL, NULL); -- all NULL fails
ERROR:  new row for relation "box" violates check constraint "exactly_one_from"
DETAIL:  Failing row contains (1, foo, null, null, null).


INSERT INTO box VALUES (2,'foo', 1, NULL, NULL);    -- 1 NULL succeeds
1 rows affected

INSERT INTO box VALUES (3,'foo', NULL, 2, NULL);    -- 1 NULL succeeds
1 rows affected

INSERT INTO box VALUES (4,'foo', 1, NULL, 3);       -- 2 NULL fails
ERROR:  new row for relation "box" violates check constraint "exactly_one_from"
DETAIL:  Failing row contains (4, foo, 1, null, 3).


TABLE box;


box_id
box_content
from_user
from_office
from_import


2
foo
1
null
null

3
foo
null
2
null


CREATE VIEW box_view AS
SELECT box_id, box_content
     , COALESCE( from_user, from_office, from_import) AS from_id
     , CASE WHEN from_user   IS NOT NULL THEN 'user'
            WHEN from_office IS NOT NULL THEN 'office'
            WHEN from_import IS NOT NULL THEN 'import' END AS from_type
FROM box;

TABLE box_view;


box_id
box_content
from_id
from_type


2
foo
1
user

3
foo
2
office