public inbox for [email protected]  

help / color / mirror / Atom feed

Postgresql range_agg() Return empty list
2+ messages / 2 participants
[nested] [flat]

* Postgresql range_agg() Return empty list
@ 2024-07-11 01:37  Han Tang <[email protected]>
  0 siblings, 1 reply; 2+ messages in thread

From: Han Tang @ 2024-07-11 01:37 UTC (permalink / raw)
  To: [email protected]

Hi

Hope you are doing well!

I am using range_agg() function, it works fine with original table value

Query Result <https://i.sstatic.net/CbOkuvXr.png;

But when I try to do some update for range value, it will give back an
empty list

Select range_agg(b.r)From (
  Select int8range(lower(bin_range)+1, upper(bin_range)+5) as r
  From bin_data) as b;

Query Result <https://i.sstatic.net/19uJeuS3.png;

I test with the same query in fiddle but no issue there

https://dbfiddle.uk/1MRn8hn6

All these query are running on pgAdmin4, wondering why it happens

Thanks
Han


Attachments:

  [image/png] Screenshot 2024-07-10 at 5.43.44 PM.png (72.3K, 3-Screenshot%202024-07-10%20at%205.43.44%E2%80%AFPM.png)
  download | view image

  [image/png] Screenshot 2024-07-10 at 5.50.40 PM.png (87.4K, 4-Screenshot%202024-07-10%20at%205.50.40%E2%80%AFPM.png)
  download | view image

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Postgresql range_agg() Return empty list
@ 2024-07-11 04:46  Paul A Jungwirth <[email protected]>
  parent: Han Tang <[email protected]>
  0 siblings, 0 replies; 2+ messages in thread

From: Paul A Jungwirth @ 2024-07-11 04:46 UTC (permalink / raw)
  To: Han Tang <[email protected]>; +Cc: [email protected]

On Wed, Jul 10, 2024 at 6:37 PM Han Tang <[email protected]> wrote:
> I am using range_agg() function, it works fine with original table value
>
> But when I try to do some update for range value, it will give back an empty list
>
> Select range_agg(b.r)
> From (
>   Select int8range(lower(bin_range)+1, upper(bin_range)+5) as r
>   From bin_data) as b;
>

The value '(,)' signifies a range with no lower bound and no upper
bound. So '{(,)}' is the multirange that contains just that range
(unbounded in either direction)---in other words not an empty list but
every integer. Ranges use null to indicate unbounded, so it is the
same as '(null,null)'. It looks like the inputs in your second
screenshot must include some ranges with null lower & upper bounds, so
then when you combine them you get everything.

It appears that query is on lines 153-156 of some larger sequence? So
I'm guessing the table contents are not the same as when you generated
the first screenshot.

If you think something else is going on, perhaps you could share the
full contents of bin_data.

Yours,
Paul






^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2024-07-11 04:46 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-07-11 01:37 Postgresql range_agg() Return empty list Han Tang <[email protected]>
2024-07-11 04:46 ` Paul A Jungwirth <[email protected]>

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox