Thread: Postgresql range_agg() Return empty list

On Wed, Jul 10, 2024 at 6:37 PM Han Tang <bj.tanghan@gmail.com> 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

Your update expression does not handle empty ranges properly, but
convert them into complete ranges.

https://dbfiddle.uk/Ia6wESpL

On Thu, Jul 11, 2024 at 5:55 PM Han Tang <bj.tanghan@gmail.com> wrote:
>
> Hi
>
> Hope you are doing well!
>
> I am using range_agg() function, it works fine with original table value
>
> Query Result
>
> 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
>
> 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
>
>