title: Pad arrays with NULL to maximum length for custom aggregate function
body: <p>From the answer of the question <a href="https://stackoverflow.com/questions/20156168/how-to-use-array-agg-for-varchar">How to use array_agg() for varchar[]</a>,</p>
  
  <p>We can create a custom aggregate function to aggregate n-dimensional arrays in Postgres like:</p>
  
  <pre><code>CREATE AGGREGATE array_agg_mult (anyarray)  (
      SFUNC     = array_cat
     ,STYPE     = anyarray
     ,INITCOND  = '{}'
  );
  </code></pre>
  
  <p>A constrain is that the values have to share the <strong>same array extents and same length</strong>, handling empty values and different lengths doesn't work.</p>
  
  <p>From the answer:</p>
  
  <blockquote>
    <p>There is no way around that, the array type does not allow such a
    mismatch in Postgres. You could pad your arrays with NULL values so
    that all dimensions have matching extents.</p>
  </blockquote>
  
  <p>I have the rows like </p>
  
  <pre><code>------ arrayfield
  
  -----  {1},
  -----  {},
  -----  {abc}
  
  array_agg_mult(ARRAY[arrayfield]) AS customarray
  </code></pre>
  
  <p>I am expecting a aggregate resule like <code>{{1},NULL,{abc}}</code> </p>
  
  <p>but it will throw </p>
  
  <pre><code>ERROR:  cannot concatenate incompatible arrays
  DETAIL:  Arrays with differing element dimensions are not compatible for concatenation.
  </code></pre>
  
  <p><strong>Is there any way that we can add the padding values in the custom function ?</strong> </p>
  
  <p>I found the issue is when the array <em>length</em> is different. <code>{a},{null},{1}</code> will aggregate, but <code>{a,b},{},{1}</code> will not. </p>
  
  <p>So I need a query where I can add NULL elements to existing arrays.</p>
  
  <p>One solution is to append two NULL always (2 is the max length going to be in that filed) <code>array_cat(arr, ARRAY[NULL,NULL])</code> and trim the array to length 2:</p>
  
  <pre><code>   {1}   --&gt; {1,NULL,NULL}     --&gt; {1,NULL}
     {NULL}  --&gt; {NULL,NULL,NULL}  --&gt; {NULL,NULL}
     {abc, def}  --&gt; {abc,def,NULL,NULL}  --&gt; {abc, def} 
  </code></pre>
  
  <p>But I can't figure out the <strong>syntax</strong>.</p>