[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re: Coalesce 2 Arrays
From: Rob Sargent <robjsargent () gmail ! com>
Date: 2019-06-24 23:28:55
Message-ID: 951467c4-229b-2bf1-5fe6-b2695b65c152 () gmail ! com
[Download RAW message or body]
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
> <mailto: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 <mailto:robjsargent@gmail.com>> wrote:
>>
>>
>>
>>> On Jun 24, 2019, at 2:31 PM, Alex Magnum
>>> <magnum11200@gmail.com <mailto: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?
[Attachment #3 (text/html)]
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body text="#000000" bgcolor="#FFFFFF">
<p><br>
</p>
<div class="moz-cite-prefix">On 6/24/19 5:19 PM, David G. Johnston
wrote:<br>
</div>
<blockquote type="cite"
cite="mid:CAKFQuwZ5_MZYpXswxmap6n+YxFXwyfOn60tABsoERexL=tNwmQ@mail.gmail.com">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<div dir="ltr">
<div dir="ltr">
<div class="gmail_default"
style="font-family:arial,helvetica,sans-serif"><span
style="font-family:Arial,Helvetica,sans-serif">On Mon, Jun
24, 2019 at 4:11 PM Rob Sargent <<a
href="mailto:robjsargent@gmail.com"
moz-do-not-send="true">robjsargent@gmail.com</a>>
wrote:</span><br>
</div>
</div>
<div class="gmail_quote">
<blockquote class="gmail_quote" style="margin:0px 0px 0px
0.8ex;border-left:1px solid
rgb(204,204,204);padding-left:1ex">
<div bgcolor="#FFFFFF">
<p><br>
</p>
<div class="gmail-m_4603085168479762280moz-cite-prefix">On
6/24/19 4:46 PM, Alex Magnum wrote:<br>
</div>
<blockquote type="cite">
<div dir="ltr">Yes, they are. <br>
</div>
<br>
<div class="gmail_quote">
<div dir="ltr" class="gmail_attr">On Tue, Jun 25, 2019
at 4:33 AM Rob Sargent <<a
href="mailto:robjsargent@gmail.com"
target="_blank" moz-do-not-send="true">robjsargent@gmail.com</a>>
wrote:<br>
</div>
<blockquote class="gmail_quote" style="margin:0px 0px
0px 0.8ex;border-left:1px solid
rgb(204,204,204);padding-left:1ex">
<div><br>
<div><br>
<blockquote type="cite">
<div>On Jun 24, 2019, at 2:31 PM, Alex Magnum
<<a href="mailto:magnum11200@gmail.com"
target="_blank" moz-do-not-send="true">magnum11200@gmail.com</a>>
wrote:</div>
<br
class="gmail-m_4603085168479762280gmail-m_-1969682809368965643Apple-interchange-newline">
<div>
<div dir="ltr">Hi,<br>
I have two arrays which I need to combine
based on the individual values;
<div>i could do a coalesce for each field
but was wondering if there is an easier
way<br>
<div><br>
</div>
<div><font face="courier new, monospace">array_a{a,
null,c, d,null,f,null} primary</font></div>
<div><font face="courier new, monospace">array_b{null,2
,null,4,5 ,6,null} secondary</font></div>
<div><font face="courier new, monospace"> <br>
</font></div>
</div>
<div><font face="courier new, monospace">result
{a, 2, c, d,5, f,null)</font></div>
<div><font face="courier new, monospace"><br>
</font></div>
<div><font face="courier new, monospace">Any
advice would be appreciated</font></div>
<div><font face="courier new, monospace"><br>
</font></div>
</div>
</div>
</blockquote>
</div>
Are the inputs always of fixed dimensions eg. 1 by
7?
<div><br>
</div>
</div>
</blockquote>
</div>
</blockquote>
create or replace function tt( a1 int[], a2 int[]) <br>
returns int[] as $$ <br>
declare <br>
aret int[]; <br>
asize int;<br>
begin<br>
select array_length(a1,1) into asize;<br>
for i in 1..asize loop<br>
aret[i] = coalesce(a1[i], a2[i]);<br>
end loop;<br>
return aret;<br>
end;<br>
<p>$$ language plpgsql;</p>
<p>select * from tt(array[3,null], array[null,4]);<br>
tt <br>
-------<br>
{3,4}<br>
(1 row<span class="gmail_default"
style="font-family:arial,helvetica,sans-serif">)</span></p>
</div>
</blockquote>
<div><span class="gmail_default"
style="font-family:arial,helvetica,sans-serif">Plain SQL
variant:</span></div>
<div><span class="gmail_default"
style="font-family:arial,helvetica,sans-serif"></span> </div>
SELECT array_agg(COALESCE(a, b))<br>
<div class="gmail_default"
style="font-family:arial,helvetica,sans-serif"><span
style="font-family:Arial,Helvetica,sans-serif">FROM (</span></div>
<div class="gmail_default"
style="font-family:arial,helvetica,sans-serif"><span
style="font-family:Arial,Helvetica,sans-serif">SELECT </span></div>
<div class="gmail_default"
style="font-family:arial,helvetica,sans-serif"><span
style="font-family:Arial,Helvetica,sans-serif">unnest(ARRAY[null,
2]::int[]),</span></div>
<div class="gmail_default"
style="font-family:arial,helvetica,sans-serif"><span
style="font-family:Arial,Helvetica,sans-serif">unnest(ARRAY[1,null]::int[])</span></div>
<div class="gmail_default"
style="font-family:arial,helvetica,sans-serif"><span
style="font-family:Arial,Helvetica,sans-serif">) vals (a,
b)</span>;</div>
<div class="gmail_default"
style="font-family:arial,helvetica,sans-serif"><br>
</div>
<div class="gmail_default"
style="font-family:arial,helvetica,sans-serif">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.</div>
<div class="gmail_default"
style="font-family:arial,helvetica,sans-serif"><br>
</div>
<div class="gmail_default"
style="font-family:arial,helvetica,sans-serif">David J.</div>
<div class="gmail_default"
style="font-family:arial,helvetica,sans-serif"><br>
</div>
</div>
</div>
</blockquote>
<p>Brilliant of course. Maybe not as easy to stick in another query</p>
<blockquote>
<p>select a.name, b.name, tt(a.intarray, b.intarray) as
coalesced_array from table a join table b on a.<something>
= b.<something>;</p>
</blockquote>
<p>Any guess at the performance differences?</p>
<p><br>
</p>
</body>
</html>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic