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 FUNCTION is_unique(input text[])
RETURNS bool AS $$
  SELECT NOT EXISTS(SELECT 1
    FROM (
        SELECT *,
          LAG(value) OVER (ORDER BY ordinal) AS prev
        FROM unnest(input) WITH ORDINALITY AS v(value, ordinal)
    ) a
    WHERE a.prev >= a.value
  ) AS result;
$$ LANGUAGE SQL
  IMMUTABLE
  RETURNS NULL ON NULL INPUT
  LEAKPROOF;

CREATE FUNCTION


CREATE TABLE IF NOT EXISTS test
(
    id SERIAL NOT NULL,
    component_type text COLLATE pg_catalog."default" NOT NULL,
    component_names text[] COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT test_pk PRIMARY KEY (id),
    CONSTRAINT cq_names CHECK ( is_unique(component_names) ),
    CONSTRAINT uq_names UNIQUE (component_names)
)

CREATE TABLE


INSERT INTO test(component_type, component_names)
VALUES ('hello', ARRAY['a', 'b']);

INSERT 0 1


INSERT INTO test(component_type, component_names)
VALUES ('hello', ARRAY['a', 'a', 'b']);

ERROR:  new row for relation "test" violates check constraint "cq_names"
DETAIL:  Failing row contains (2, hello, {a,a,b}).


INSERT INTO test(component_type, component_names)
VALUES ('hello', ARRAY['b', 'a', 'b']);

ERROR:  new row for relation "test" violates check constraint "cq_names"
DETAIL:  Failing row contains (3, hello, {b,a,b}).


INSERT INTO test(component_type, component_names)
VALUES ('hello', ARRAY['a', 'b']);

ERROR:  duplicate key value violates unique constraint "uq_names"
DETAIL:  Key (component_names)=({a,b}) already exists.