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 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
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