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 OR REPLACE FUNCTION arraycol (v anyelement, n int, i int, OUT r anyarray)
   LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE AS
$func$
BEGIN
   r := array_fill(NULLIF(v, v), ARRAY[n]);
   -- if i > n then this assignment auto-fills positions in between with null
   r[i] := v;
END
$func$;

CREATE FUNCTION

SELECT arraycol (1, 3, 2);
SELECT arraycol (1, 3, 4);

SELECT arraycol ('2024-1-1'::date, 3, 2);
SELECT arraycol ('2024-1-1'::date, 3, 4);

arraycol

{NULL,1,NULL}

SELECT 1

arraycol

{NULL,NULL,NULL,1}

SELECT 1

arraycol

{NULL,2024-01-01,NULL}

SELECT 1

arraycol

{NULL,NULL,NULL,2024-01-01}

SELECT 1

CREATE OR REPLACE FUNCTION arraycol2 (v anyelement, n int, i int, OUT r anyarray)
   LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE AS
$func$
BEGIN
   r[1] := null;  -- assuming 1-based indexes!
   r[i] := v;     -- auto-fills positions in between with null
   IF n > i THEN
      r[n] := null;
   END IF;
END
$func$;

CREATE FUNCTION

SELECT arraycol2 (1, 3, 2);
SELECT arraycol2 (1, 3, 4);

SELECT arraycol2 ('2024-1-1'::date, 3, 2);
SELECT arraycol2 ('2024-1-1'::date, 3, 4);

arraycol2

{NULL,1,NULL}

SELECT 1

arraycol2

{NULL,NULL,NULL,1}

SELECT 1

arraycol2

{NULL,2024-01-01,NULL}

SELECT 1

arraycol2

{NULL,NULL,NULL,2024-01-01}

SELECT 1