Error when using array_agg with filter where clause in pg16 and pg17

From: Kaimeh <kkaimeh(at)gmail(dot)com>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Error when using array_agg with filter where clause in pg16 and pg17
Date: 2025-04-08 12:38:17
Message-ID: CAK-+Jz9J=Q06-M7cDJoPNeYbz5EZDqkjQbJnmRyQyzkbRGsYkA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Hello!

In postgresql 16 and 17 using array_agg with filter where gives an error,
while in postgres 15 exact same query works.

This is minimal sample for reproducing:

create table test (id int, data jsonb);

insert into test (id, data) values
(1, '{"a": null}'),
(2, '{"a": "2"}'),
(3, '{"a": "2"}'),
(4, '{"a": ""}');

select array_agg(distinct (data->>'a')::int) filter (where data->>'a' is
not null and data->>'a' != '')
from test;

Last query in pg16 or pg17 returns ERROR #22P02 invalid input syntax for
type integer: ""
In pg15 it returns correct result {2}

wbr, Ferossa.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message PG Bug reporting form 2025-04-08 13:56:57 BUG #18884: The CURRENT_TIMESTAMP value being returned in UTC-2.
Previous Message David G. Johnston 2025-04-08 12:29:53 Re: BUG #18883: Epoch issue