Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create function arraycol(
  v anycompatiblenonarray,
  n int,
  i int, 
  r anyarray default array[null])
  returns anycompatiblearray language plpgsql as $f$ 
begin
    r=array_fill(nullif(v,v),array[n]);
    r[i]=v;
    return r;
end $f$;
select arraycol(1,3,2);
select arraycol('some_text',3,2);
select arraycol(true,3,2);
select arraycol('{"a":true}'::jsonb,3,2);
select arraycol(tstzrange('now','tomorrow','[]'),3,2);
--select arraycol(array[1,2,3],3,2);

CREATE FUNCTION

arraycol

{NULL,1,NULL}

SELECT 1

arraycol

{NULL,some_text,NULL}

SELECT 1

arraycol

{NULL,t,NULL}

SELECT 1

arraycol

{NULL,"{"a": true}",NULL}

SELECT 1

arraycol

{NULL,"["2024-05-10 15:20:18.058346+01","2024-05-11 00:00:00+01"]",NULL}

SELECT 1

create function arraycol2(
  v anycompatiblenonarray,
  n int,
  i int, 
  r anyarray default array[null])
  returns anycompatiblearray immutable strict parallel safe language plpgsql as $f$ 
begin
    r=array_fill(nullif(v,v),array[n]);
    r[i]=v;
    return r;
end $f$;
select arraycol(1,3,2);
select arraycol('some_text',3,2);
select arraycol(true,3,2);
select arraycol('{"a":true}'::jsonb,3,2);
select arraycol(tstzrange('now','tomorrow','[]'),3,2);
--select arraycol(array[1,2,3],3,2);

CREATE FUNCTION

arraycol

{NULL,1,NULL}

SELECT 1

arraycol

{NULL,some_text,NULL}

SELECT 1

arraycol

{NULL,t,NULL}

SELECT 1

arraycol

{NULL,"{"a": true}",NULL}

SELECT 1

arraycol

{NULL,"["2024-05-10 15:20:18.072587+01","2024-05-11 00:00:00+01"]",NULL}

SELECT 1

create function arraycol(
  v anycompatible,
  n int,
  i int, 
  r anyarray default array[null])
  returns anycompatiblearray language plpgsql as $f$ 
begin
    r=array_fill(nullif(v,v),array[n]);
    r[i]=v;
    return r;
end $f$;
select arraycol(1,3,2);
select arraycol('some_text',3,2);
select arraycol(true,3,2);
select arraycol('{"a":true}'::jsonb,3,2);
select arraycol(tstzrange('now','tomorrow','[]'),3,2);
select arraycol(array[1,2,3],3,2);

CREATE FUNCTION

ERROR:  function arraycol(integer, integer, integer) is not unique
LINE 12: select arraycol(1,3,2);
                ^
HINT:  Could not choose a best candidate function. You might need to add explicit type casts.

create function arraycol(
  v anyelement,--you'll need explicit casts here and there
  n int,
  i int, 
  r anycompatiblearray default array[null])
  returns anycompatiblearray language plpgsql as $f$ 
begin
    r=array_fill(nullif(v,v),array[n]);
    r[i]=v;
    return r;
end $f$;
select arraycol(1,3,2);
select arraycol('some_text'::text,3,2);
select arraycol(true,3,2);
select arraycol('{"a":true}'::jsonb,3,2);
select arraycol(tstzrange('now','tomorrow','[]'),3,2);
select arraycol(array[1,2,3],3,2);

CREATE FUNCTION

ERROR:  function arraycol(integer, integer, integer) is not unique
LINE 12: select arraycol(1,3,2);
                ^
HINT:  Could not choose a best candidate function. You might need to add explicit type casts.

create function arraycol_arr(
  v anycompatiblenonarray,
  n int,
  i int, 
  r anyarray default array[null])
  returns anycompatiblearray immutable strict parallel safe as $f$ 
select arr[:i-1]||array[v]||arr[i+1:]
from(select array_fill(nullif(v,v),array[n]) arr)_;
$f$ language sql;
select arraycol_arr(1,3,2);
select arraycol_arr('some_text',3,2);
select arraycol_arr(true,3,2);
select arraycol_arr('{"a":true}'::jsonb,3,2);
select arraycol_arr(tstzrange('now','tomorrow','[]'),3,2);
--select arraycol_arr(array[1,2,3],3,2);

CREATE FUNCTION

arraycol_arr

{NULL,1,NULL}

SELECT 1

arraycol_arr

{NULL,some_text,NULL}

SELECT 1

arraycol_arr

{NULL,t,NULL}

SELECT 1

arraycol_arr

{NULL,"{"a": true}",NULL}

SELECT 1

arraycol_arr

{NULL,"["2024-05-10 15:20:18.075239+01","2024-05-11 00:00:00+01"]",NULL}

SELECT 1

create function arraycol_sql_gens_agg(
  v anycompatiblenonarray,
  n int,
  i int, 
  r anyarray default array[null])
  returns anycompatiblearray immutable strict parallel safe as $f$ 
select array_agg(case x when i then v end)
from(select generate_series(1,n)x)_;
$f$ language sql;
select arraycol_sql_gens_agg(1,3,2);
select arraycol_sql_gens_agg('some_text',3,2);
select arraycol_sql_gens_agg(true,3,2);
select arraycol_sql_gens_agg('{"a":true}'::jsonb,3,2);
select arraycol_sql_gens_agg(tstzrange('now','tomorrow','[]'),3,2);
--select arraycol_sql_gens_agg(array[1,2,3],3,2);

CREATE FUNCTION

arraycol_sql_gens_agg

{NULL,1,NULL}

SELECT 1

arraycol_sql_gens_agg

{NULL,some_text,NULL}

SELECT 1

arraycol_sql_gens_agg

{NULL,t,NULL}

SELECT 1

arraycol_sql_gens_agg

{NULL,"{"a": true}",NULL}

SELECT 1

arraycol_sql_gens_agg

{NULL,"["2024-05-10 15:20:18.081948+01","2024-05-11 00:00:00+01"]",NULL}

SELECT 1

--https://stackoverflow.com/a/78452034/5298879
CREATE OR REPLACE FUNCTION arraycol_erwin (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

--https://stackoverflow.com/a/78452034/5298879
--adding `compatible` to avoid having to explicitly cast input values
CREATE OR REPLACE FUNCTION arraycol_erwin2 (
  v anycompatible, n int, i int, OUT r anycompatiblearray)
   LANGUAGE plpgsql IMMUTABLE STRICT 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

drop table if exists timings;
create table timings(variant text,duration interval);
do $f$
  declare i int:=1;
          start_time timestamptz;
          end_time timestamptz;
          number_of_tests int:=1000;
          max_array_length int:=10000;
begin
  perform setseed(.42);
  start_time:=clock_timestamp();
  for i in 1..number_of_tests loop
    perform arraycol(1,r,(r*random())::int) from (select (1+random()*max_array_length)::int as r)_;
    perform arraycol('some_text',r,(r*random())::int) from (select (1+random()*max_array_length)::int as r)_;
    perform arraycol(true,r,(r*random())::int) from (select (1+random()*max_array_length)::int as r)_;
    perform arraycol('{"a":true}'::jsonb,r,(r*random())::int) from (select (1+random()*max_array_length)::int as r)_;
    perform arraycol(tstzrange('now','tomorrow','[]'),r,(r*random())::int) from (select (1+random()*max_array_length)::int as r)_;
  end loop;
  end_time:=clock_timestamp();
  insert into timings values ('arraycol',end_time-start_time);
    
  perform setseed(.42);
  start_time:=clock_timestamp();
  for i in 1..number_of_tests loop
    perform arraycol_erwin2(1,r,(r*random())::int) from (select (1+random()*max_array_length)::int as r)_;
    perform arraycol_erwin2('some_text',r,(r*random())::int) from (select (1+random()*max_array_length)::int as r)_;
    perform arraycol_erwin2(true,r,(r*random())::int) from (select (1+random()*max_array_length)::int as r)_;
    perform arraycol_erwin2('{"a":true}'::jsonb,r,(r*random())::int) from (select (1+random()*max_array_length)::int as r)_;
    perform arraycol_erwin2(tstzrange('now','tomorrow','[]'),r,(r*random())::int) from (select (1+random()*max_array_length)::int as r)_;
  end loop;
  end_time:=clock_timestamp();
  insert into timings values ('arraycol_erwin2',end_time-start_time);
    
  perform setseed(.42);
  start_time:=clock_timestamp();
  for i in 1..number_of_tests loop
    perform arraycol_erwin(1,r,(r*random())::int) from (select (1+random()*max_array_length)::int as r)_;
    perform arraycol_erwin('some_text'::text,r,(r*random())::int) from (select (1+random()*max_array_length)::int as r)_;
    perform arraycol_erwin(true,r,(r*random())::int) from (select (1+random()*max_array_length)::int as r)_;
    perform arraycol_erwin('{"a":true}'::jsonb,r,(r*random())::int) from (select (1+random()*max_array_length)::int as r)_;
    perform arraycol_erwin(tstzrange('now','tomorrow','[]'),r,(r*random())::int) from (select (1+random()*max_array_length)::int as r)_;
  end loop;
  end_time:=clock_timestamp();
  insert into timings values ('arraycol_erwin',end_time-start_time);
    
  perform setseed(.42);
  start_time:=clock_timestamp();
  for i in 1..number_of_tests loop
    perform arraycol2(1,r,(r*random())::int) from (select (1+random()*max_array_length)::int as r)_;
    perform arraycol2('some_text',r,(r*random())::int) from (select (1+random()*max_array_length)::int as r)_;
    perform arraycol2(true,r,(r*random())::int) from (select (1+random()*max_array_length)::int as r)_;
    perform arraycol2('{"a":true}'::jsonb,r,(r*random())::int) from (select (1+random()*max_array_length)::int as r)_;
    perform arraycol2(tstzrange('now','tomorrow','[]'),r,(r*random())::int) from (select (1+random()*max_array_length)::int as r)_;
  end loop;
  end_time:=clock_timestamp();
  insert into timings values ('arraycol2',end_time-start_time);
    
  perform setseed(.42);
  start_time:=clock_timestamp();
  for i in 1..number_of_tests loop
    perform arraycol_sql_gens_agg(1,r,(r*random())::int) from (select (1+random()*max_array_length)::int as r)_;
    perform arraycol_sql_gens_agg('some_text',r,(r*random())::int) from (select (1+random()*max_array_length)::int as r)_;
    perform arraycol_sql_gens_agg(true,r,(r*random())::int) from (select (1+random()*max_array_length)::int as r)_;
    perform arraycol_sql_gens_agg('{"a":true}'::jsonb,r,(r*random())::int) from (select (1+random()*max_array_length)::int as r)_;
    perform arraycol_sql_gens_agg(tstzrange('now','tomorrow','[]'),r,(r*random())::int) from (select (1+random()*max_array_length)::int as r)_;
  end loop;
  end_time:=clock_timestamp();
  insert into timings values ('arraycol_sql_gens_agg',end_time-start_time);
    
  perform setseed(.42);
  start_time:=clock_timestamp();
  for i in 1..number_of_tests loop
    perform arraycol_arr(1,r,(r*random())::int) from (select (1+random()*max_array_length)::int as r)_;
    perform arraycol_arr('some_text',r,(r*random())::int) from (select (1+random()*max_array_length)::int as r)_;
    perform arraycol_arr(true,r,(r*random())::int) from (select (1+random()*max_array_length)::int as r)_;
    perform arraycol_arr('{"a":true}'::jsonb,r,(r*random())::int) from (select (1+random()*max_array_length)::int as r)_;
    perform arraycol_arr(tstzrange('now','tomorrow','[]'),r,(r*random())::int) from (select (1+random()*max_array_length)::int as r)_;
  end loop;
  end_time:=clock_timestamp();
  insert into timings values ('arraycol_arr',end_time-start_time);
end $f$;

select * from timings order by duration;

DROP TABLE

CREATE TABLE

DO

variant
duration

arraycol_erwin
00:00:00.199479

arraycol_erwin2
00:00:00.21805

arraycol2
00:00:00.857889

arraycol
00:00:01.016517

arraycol_arr
00:00:01.676274

arraycol_sql_gens_agg
00:00:03.468328

SELECT 6
variant	duration
arraycol_erwin	00:00:00.199479
arraycol_erwin2	00:00:00.21805
arraycol2	00:00:00.857889
arraycol	00:00:01.016517
arraycol_arr	00:00:01.676274
arraycol_sql_gens_agg	00:00:03.468328