Thread: Coalesce 2 Arrays

Coalesce 2 Arrays

From
Alex Magnum
Date:
Hi,
I have two arrays which I need to combine based on the individual values;
i could do a coalesce for each field but was wondering if there is an easier way

array_a{a,   null,c,   d,null,f,null}  primary
array_b{null,2   ,null,4,5   ,6,null}  secondary
 
result {a,   2,   c,   d,5,   f,null)

Any advice would be appreciated

Re: Coalesce 2 Arrays

From
Rob Sargent
Date:


On Jun 24, 2019, at 2:31 PM, Alex Magnum <magnum11200@gmail.com> wrote:

Hi,
I have two arrays which I need to combine based on the individual values;
i could do a coalesce for each field but was wondering if there is an easier way

array_a{a,   null,c,   d,null,f,null}  primary
array_b{null,2   ,null,4,5   ,6,null}  secondary
 
result {a,   2,   c,   d,5,   f,null)

Any advice would be appreciated

Are the inputs always of fixed dimensions eg. 1 by 7?

Re: Coalesce 2 Arrays

From
Alex Magnum
Date:
Yes, they are. 

On Tue, Jun 25, 2019 at 4:33 AM Rob Sargent <robjsargent@gmail.com> wrote:


On Jun 24, 2019, at 2:31 PM, Alex Magnum <magnum11200@gmail.com> wrote:

Hi,
I have two arrays which I need to combine based on the individual values;
i could do a coalesce for each field but was wondering if there is an easier way

array_a{a,   null,c,   d,null,f,null}  primary
array_b{null,2   ,null,4,5   ,6,null}  secondary
 
result {a,   2,   c,   d,5,   f,null)

Any advice would be appreciated

Are the inputs always of fixed dimensions eg. 1 by 7?

Re: Coalesce 2 Arrays

From
Rob Sargent
Date:


On 6/24/19 4:46 PM, Alex Magnum wrote:
Yes, they are. 

On Tue, Jun 25, 2019 at 4:33 AM Rob Sargent <robjsargent@gmail.com> wrote:


On Jun 24, 2019, at 2:31 PM, Alex Magnum <magnum11200@gmail.com> wrote:

Hi,
I have two arrays which I need to combine based on the individual values;
i could do a coalesce for each field but was wondering if there is an easier way

array_a{a,   null,c,   d,null,f,null}  primary
array_b{null,2   ,null,4,5   ,6,null}  secondary
 
result {a,   2,   c,   d,5,   f,null)

Any advice would be appreciated

Are the inputs always of fixed dimensions eg. 1 by 7?

create or replace function tt( a1 int[], a2 int[])
returns int[] as $$
declare
        aret int[];
        asize int;
begin
     select array_length(a1,1) into asize;
     for i in 1..asize loop
          aret[i] = coalesce(a1[i], a2[i]);
     end loop;
     return aret;
end;

$$ language plpgsql;

select * from tt(array[3,null], array[null,4]);
  tt  
-------
 {3,4}
(1 row)


Re: Coalesce 2 Arrays

From
Rob Sargent
Date:


On 6/24/19 4:46 PM, Alex Magnum wrote:
Yes, they are. 

On Tue, Jun 25, 2019 at 4:33 AM Rob Sargent <robjsargent@gmail.com> wrote:


On Jun 24, 2019, at 2:31 PM, Alex Magnum <magnum11200@gmail.com> wrote:

Hi,
I have two arrays which I need to combine based on the individual values;
i could do a coalesce for each field but was wondering if there is an easier way

array_a{a,   null,c,   d,null,f,null}  primary
array_b{null,2   ,null,4,5   ,6,null}  secondary
 
result {a,   2,   c,   d,5,   f,null)

Any advice would be appreciated

Are the inputs always of fixed dimensions eg. 1 by 7?

create or replace function tt( a1 int[], a2 int[])
returns int[] as $$
declare
        aret int[];
        asize int;
begin
     select array_length(a1,1) into asize;
     for i in 1..asize loop
          aret[i] = coalesce(a1[i], a2[i]);
     end loop;
     return aret;
end;

$$ language plpgsql; select * from tt(array[3,null], array[null,4]);
  tt  
-------
 {3,4}
(1 row)



Re: Coalesce 2 Arrays

From
"David G. Johnston"
Date:
On Mon, Jun 24, 2019 at 4:11 PM Rob Sargent <robjsargent@gmail.com> wrote:


On 6/24/19 4:46 PM, Alex Magnum wrote:
Yes, they are. 

On Tue, Jun 25, 2019 at 4:33 AM Rob Sargent <robjsargent@gmail.com> wrote:


On Jun 24, 2019, at 2:31 PM, Alex Magnum <magnum11200@gmail.com> wrote:

Hi,
I have two arrays which I need to combine based on the individual values;
i could do a coalesce for each field but was wondering if there is an easier way

array_a{a,   null,c,   d,null,f,null}  primary
array_b{null,2   ,null,4,5   ,6,null}  secondary
 
result {a,   2,   c,   d,5,   f,null)

Any advice would be appreciated

Are the inputs always of fixed dimensions eg. 1 by 7?

create or replace function tt( a1 int[], a2 int[])
returns int[] as $$
declare
        aret int[];
        asize int;
begin
     select array_length(a1,1) into asize;
     for i in 1..asize loop
          aret[i] = coalesce(a1[i], a2[i]);
     end loop;
     return aret;
end;

$$ language plpgsql;

select * from tt(array[3,null], array[null,4]);
  tt  
-------
 {3,4}
(1 row)

Plain SQL variant:
 
SELECT array_agg(COALESCE(a, b))
FROM (
SELECT 
unnest(ARRAY[null, 2]::int[]),
unnest(ARRAY[1,null]::int[])
) vals (a, b);

Even if they aren't the same length the above should work, I think, as extra rows for the shorter array will contribute padded nulls.

David J.

Re: Coalesce 2 Arrays

From
Rob Sargent
Date:


On 6/24/19 5:19 PM, David G. Johnston wrote:
On Mon, Jun 24, 2019 at 4:11 PM Rob Sargent <robjsargent@gmail.com> wrote:


On 6/24/19 4:46 PM, Alex Magnum wrote:
Yes, they are. 

On Tue, Jun 25, 2019 at 4:33 AM Rob Sargent <robjsargent@gmail.com> wrote:


On Jun 24, 2019, at 2:31 PM, Alex Magnum <magnum11200@gmail.com> wrote:

Hi,
I have two arrays which I need to combine based on the individual values;
i could do a coalesce for each field but was wondering if there is an easier way

array_a{a,   null,c,   d,null,f,null}  primary
array_b{null,2   ,null,4,5   ,6,null}  secondary
 
result {a,   2,   c,   d,5,   f,null)

Any advice would be appreciated

Are the inputs always of fixed dimensions eg. 1 by 7?

create or replace function tt( a1 int[], a2 int[])
returns int[] as $$
declare
        aret int[];
        asize int;
begin
     select array_length(a1,1) into asize;
     for i in 1..asize loop
          aret[i] = coalesce(a1[i], a2[i]);
     end loop;
     return aret;
end;

$$ language plpgsql;

select * from tt(array[3,null], array[null,4]);
  tt  
-------
 {3,4}
(1 row)

Plain SQL variant:
 
SELECT array_agg(COALESCE(a, b))
FROM (
SELECT 
unnest(ARRAY[null, 2]::int[]),
unnest(ARRAY[1,null]::int[])
) vals (a, b);

Even if they aren't the same length the above should work, I think, as extra rows for the shorter array will contribute padded nulls.

David J.

Brilliant of course.  Maybe not as easy to stick in another query

select a.name, b.name, tt(a.intarray, b.intarray) as coalesced_array from table a join table b on a.<something> = b.<something>;

Any guess at the performance differences?