1

This is almost same question as Pad arrays with NULL to maximum length for custom aggregate function. the only problem with the answer provided is that function array_fill doesn't exist in Postgres 8.2.

How can I make it useable given the version I have?

Community
  • 1
  • 1
fairybetty
  • 57
  • 7

2 Answers2

1
do $$
declare
  arr int[] := array[1,2,3];
begin
  raise info '%', arr;
  arr[1] := coalesce(arr[1]); -- Set lower bound if array is null
  arr[10] := coalesce(arr[10]); -- Set upper bound
  raise info '%', arr;
end $$;

Output:

INFO:  {1,2,3}
INFO:  {1,2,3,NULL,NULL,NULL,NULL,NULL,NULL,NULL}

Not a complete answer, but it is easy to convert it to the function.

Disclaimer: Tested on PostgreSQL 9.5

Abelisto
  • 14,826
  • 2
  • 33
  • 41
0

This should work:

CREATE TEMP TABLE t AS (SELECT arr::text[] FROM UNNEST('{"{1}","{}","{a,b,c}"}'::text[]) arr);
SELECT COALESCE(NULLIF(arr || nulls[1:max-(1+array_upper(arr, 1)-array_lower(arr, 1))], '{}'), nulls[1:max])
FROM t,
  (SELECT ARRAY(SELECT NULL::text FROM generate_series(1, 100)) AS nulls) a,
  (SELECT 1 + max(array_upper(arr, 1)-array_lower(arr, 1)) AS max FROM t) m
Ezequiel Tolnay
  • 4,302
  • 1
  • 19
  • 28
  • the array I tried to pad with null is an integer array. Do I have to convert it to text array? got ERROR: operator does not exist: integer[] || text[] LINE 2: COALESCE(NULLIF(arr || nulls[1:6-(1+array... ^ HINT: No operator matches the given name and argument type(s). You may need to add explicit type casts. – fairybetty Jul 07 '16 at 11:19
  • @fairybetty just change the `::text[]` and `::text` to int :) – Ezequiel Tolnay Jul 07 '16 at 11:22