Subject: Postgresql range_agg() Return empty list

From: Han Tang <[email-masked]>
Date: Wed, 10 Jul 2024 18:37:11 -0700

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

---

From: Paul A Jungwirth <[email-masked]>
Date: Wed, 10 Jul 2024 21:46:33 -0700

On Wed, Jul 10, 2024 at 6:37 PM Han Tang <[email-masked]> wrote:
> But when I try to do some update for range value, it will give back an empty list
>
> Select range_agg(b.r)
> From (

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