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 function check_duplicates (my_array text[]) 
returns boolean
language sql
immutable
as
$$
 SELECT cardinality(my_array) = cardinality(array_agg(DISTINCT u))
 FROM unnest(my_array) u;
$$;
create table address 
( 
    cities text[] not null,
    CONSTRAINT 
        check_cities_are_not_duplicate 
        CHECK(check_duplicates(cities))
);
INSERT INTO address VALUES
(ARRAY['A', 'B']);


1 rows affected

INSERT INTO address VALUES
(ARRAY['C', 'C']);
ERROR:  new row for relation "address" violates check constraint "check_cities_are_not_duplicate"
DETAIL:  Failing row contains ({C,C}).


SELECT* FROM address


cities


{A,B}