MIME-Version: 1.0
References: <CAAqOU0s08dTFu0tQgEg=q+ifeU4aSYwxCG=ae229PrwwAea2mA@mail.gmail.com>
In-Reply-To: <CAAqOU0s08dTFu0tQgEg=q+ifeU4aSYwxCG=ae229PrwwAea2mA@mail.gmail.com>
From: Paul A Jungwirth <pj@illuminatedcomputing.com>
Date: Wed, 10 Jul 2024 21:46:33 -0700
Message-ID: <CA+renyVrWZzX5NcKPyCD5sZQbf6pGOi=7wg=iAWDPXnt+YndTw@mail.gmail.com>
Subject: Re: Postgresql range_agg() Return empty list
To: Han Tang <bj.tanghan@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CA%2BrenyVrWZzX5NcKPyCD5sZQbf6pGOi%3D7wg%3DiAWDPXnt%2BYndTw%40mail.gmail.com>
Precedence: bulk

On Wed, Jul 10, 2024 at 6:37=E2=80=AFPM Han Tang <bj.tanghan@gmail.com> wro=
te:
> 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 em=
pty 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