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 FUNCTION

create table address 
( 
    cities text[] not null,
    CONSTRAINT 
        check_cities_are_not_duplicate 
        CHECK(check_duplicates(cities))
);

CREATE TABLE

INSERT INTO address VALUES
(ARRAY['A', 'B']);

INSERT 0 1

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}

SELECT 1