Horton
Menu

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

Kaimeh <kkaimeh@gmail.com>

View thread
From: Kaimeh <kkaimeh@gmail.com>
To: pgsql-bugs@lists.postgresql.org
Date: 2025-04-08T12:38:17Z
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.

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Fix issue with ORDER BY / DISTINCT aggregates and FILTER

  2. Improve performance of ORDER BY / DISTINCT aggregates