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 combinations (
    id serial PRIMARY KEY,
    some_id1 integer NOT NULL,
    some_id2 integer NOT NULL,
    some_id3 integer NOT NULL
);
CREATE UNIQUE INDEX ON combinations(some_id1, some_id2, some_id3);
CREATE OR REPLACE FUNCTION unnest_2d_1d(anyarray)
  RETURNS SETOF anyarray AS
$func$
SELECT array_agg($1[d1][d2])
FROM   generate_subscripts($1,1) d1
    ,  generate_subscripts($1,2) d2
GROUP  BY d1
ORDER  BY d1
$func$  LANGUAGE sql IMMUTABLE;
SELECT unnest_2d_1d('{{1, 10, 100}, {2, 11, 101}}'::int[][])


unnest_2d_1d


{1,10,100}

{2,11,101}


CREATE OR REPLACE function create_combinations_if_needed(p_combinations integer[][]) RETURNS boolean
LANGUAGE sql AS
$$
   INSERT INTO combinations (some_id1, some_id2, some_id3)
   SELECT unnest[1], unnest[2], unnest[3]
   FROM unnest_2d_1d(p_combinations) as unnest
   ON CONFLICT (some_id1, some_id2, some_id3)
   DO NOTHING
   RETURNING TRUE;
$$;
SELECT create_combinations_if_needed('{{1, 10, 100}, {2, 11, 101}}')


create_combinations_if_needed


t


SELECT * FROM combinations


id
some_id1
some_id2
some_id3


1
1
10
100

2
2
11
101