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

Поиск
Список
Период
Сортировка
От Kaimeh
Тема Error when using array_agg with filter where clause in pg16 and pg17
Дата
Msg-id CAK-+Jz9J=Q06-M7cDJoPNeYbz5EZDqkjQbJnmRyQyzkbRGsYkA@mail.gmail.com
обсуждение исходный текст
Ответы Re: Error when using array_agg with filter where clause in pg16 and pg17
Список 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.

В списке pgsql-bugs по дате отправления: