Postgres 11

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 foo 
( 
    scopes text[] not null,
    CONSTRAINT 
        all_parts_of_array 
        CHECK(scopes <@ ARRAY['read', 'write', 'delete', 'update'])
);

CREATE TABLE

INSERT INTO foo VALUES
('{delete, update}');

INSERT 0 1

INSERT INTO foo VALUES
('{delete, update, read}');

INSERT 0 1

INSERT INTO foo VALUES
('{delete, update, DRINK}');

ERROR:  new row for relation "foo" violates check constraint "all_parts_of_array"
DETAIL:  Failing row contains ({delete,update,DRINK}).

INSERT INTO foo VALUES
('{delete, update, delete, delete}');

INSERT 0 1

SELECT * FROM foo

scopes

{delete,update}

{delete,update,read}

{delete,update,delete,delete}

SELECT 3