agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedQuestion about WITH ORDINALITY and unnest
4+ messages / 4 participants
[nested] [flat]
* Question about WITH ORDINALITY and unnest
@ 2021-01-23 13:17 Mike Martin <redtux1@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Mike Martin @ 2021-01-23 13:17 UTC (permalink / raw)
To: pgsql-sql <pgsql-sql@lists.postgresql.org>
Hi
I have a strange case with ordinality and row number with multiple
rows/arrays.
The row numbering is per array, rather than for the whole set. ie
I have a two row data set with
row 1 {val1,val2,val3,val4,val5}
row 2 {vala,valb}
Then the
result of
SELECT DISTINCT
array_agg(uargs ORDER BY nr),
og.transref,fileid
FROM og,UNNEST (groupargs) WITH ORDINALITY uarg(uargs,nr)
GROUP By og.transref,fileid
(og is a cte with grouping of trasref and fileid)
is
{val1,val1,val2,val2,val3,val4,val5}
or without the grouping
val1,1
vala,1
val2,2
valb,2
val3,3
val4,4
val5,5
which really messes up the ordering of the array.
Is there any way to make the ordinality ordering to be over the entire
record set rather than per array
(the idea is to combine two arrays of varying dimensions into one, keeping
order)
I have got a working solution by adding a rowid and ordering the array by
eg: rowid+nr (where rowid is 100,200, big enough not to be in record set)
thanks
Mike
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Question about WITH ORDINALITY and unnest
@ 2021-01-23 16:14 David G. Johnston <david.g.johnston@gmail.com>
parent: Mike Martin <redtux1@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: David G. Johnston @ 2021-01-23 16:14 UTC (permalink / raw)
To: Mike Martin <redtux1@gmail.com>; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
On Saturday, January 23, 2021, Mike Martin <redtux1@gmail.com> wrote:
>
>
> Is there any way to make the ordinality ordering to be over the entire
> record set rather than per array
>
No
>
> (the idea is to combine two arrays of varying dimensions into one, keeping
> order)
>
> I have got a working solution by adding a rowid and ordering the array by
> eg: rowid+nr (where rowid is 100,200, big enough not to be in record set)
>
Yep, this is what I often do as well.
David J.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Question about WITH ORDINALITY and unnest
@ 2021-01-23 21:14 Shaozhong SHI <shishaozhong@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Shaozhong SHI @ 2021-01-23 21:14 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Mike Martin <redtux1@gmail.com>; pgsql-sql <pgsql-sql@lists.postgresql.org>
The most difficult part is unequal numbers of elements in arrays. For
instance, not well structured, delimited addresses.
Has anyone got a nice way to deal with unequal numbers of elements in
arrays?
Regards,
David
On Sat, 23 Jan 2021 at 16:14, David G. Johnston <david.g.johnston@gmail.com>
wrote:
> On Saturday, January 23, 2021, Mike Martin <redtux1@gmail.com> wrote:
>>
>>
>> Is there any way to make the ordinality ordering to be over the entire
>> record set rather than per array
>>
>
> No
>
>
>>
>> (the idea is to combine two arrays of varying dimensions into one,
>> keeping order)
>>
>> I have got a working solution by adding a rowid and ordering the array by
>> eg: rowid+nr (where rowid is 100,200, big enough not to be in record set)
>>
>
> Yep, this is what I often do as well.
>
> David J.
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Question about WITH ORDINALITY and unnest
@ 2021-01-23 21:50 Tom Lane <tgl@sss.pgh.pa.us>
parent: Shaozhong SHI <shishaozhong@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2021-01-23 21:50 UTC (permalink / raw)
To: Shaozhong SHI <shishaozhong@gmail.com>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; Mike Martin <redtux1@gmail.com>; pgsql-sql <pgsql-sql@lists.postgresql.org>
Shaozhong SHI <shishaozhong@gmail.com> writes:
> Has anyone got a nice way to deal with unequal numbers of elements in
> arrays?
This might or might not be what you want, but:
db=# select * from unnest(ARRAY[1,2], ARRAY['foo','bar','baz']) as x(a,b);
a | b
---+-----
1 | foo
2 | bar
| baz
(3 rows)
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2021-01-23 21:50 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-01-23 13:17 Question about WITH ORDINALITY and unnest Mike Martin <redtux1@gmail.com>
2021-01-23 16:14 ` David G. Johnston <david.g.johnston@gmail.com>
2021-01-23 21:14 ` Shaozhong SHI <shishaozhong@gmail.com>
2021-01-23 21:50 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox