Why Postgres jsonb_agg function is giving error operation & does not exist?

Question

I'm running to this problem and haven't found any information related to it. On the same Postgres server (version 13.2), the below query works in one database, but is giving "ERROR: operator does not exist: jsonb & jsonb" in another database.

select jsonb_agg('{"a": "b", "c": "d"}'::jsonb - 'a');

This is the full error output:

ERROR:  operator does not exist: jsonb & jsonb
LINE 1: SELECT json_cur_in & json_next_in
                           ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
QUERY:  SELECT json_cur_in & json_next_in
CONTEXT:  PL/pgSQL function jsonb_agg_statef(jsonb,jsonb) line 3 at assignment
SQL state: 42883

The select query above is a simplified example, not the actual code. But it shows the same error. The jsonb_agg is the built-in function.

Any help is greatly appreciated.

What's the reason for the jsonb_agg in your example? You are not aggregating anything. But the query works just fine there must be something you are not telling us. — user330315
– user330315, Commented Aug 23, 2022 at 19:44
Someone been fooling around in the system catalogs? Or did someone create their own version of jsonb_agg and overload the function? In psql \df *.jsonb_agg will show you the latter. The system supplied should have a line like: pg_catalog | jsonb_agg | jsonb | anyelement | agg — Adrian Klaver
– Adrian Klaver, Commented Aug 23, 2022 at 22:23
The error is correct there is no & operator for jsonb, so I'm guessing someone has been getting inventive. — Adrian Klaver
– Adrian Klaver, Commented Aug 23, 2022 at 22:29
The error is coming from the user function jsonb_agg_statef in the query SELECT json_cur_in & json_next_in. That is not going to work as again there is no & operator for jsonb. — Adrian Klaver
– Adrian Klaver, Commented Aug 23, 2022 at 22:57
Thanks to @AdrianKlaver, I've found the problem. Someone created their own version of jsonb_agg and jsonb_agg_statef and put that in the public schema. Problem solved! — andynqd
– andynqd, Commented Aug 23, 2022 at 23:34

Adrian Klaver · Accepted Answer · 2022-08-24 04:23:18Z

1

Running select jsonb_agg('{"a": "b", "c": "d"}'::jsonb - 'a'); and getting this error ERROR: operator does not exist: jsonb & jsonb" pointed at there being another version of jsonb_agg() being play as there is no & operator for jsonb. This indicates a user created function, by a user who did not actually test it, is overloading jsonb_agg(). In psql doing \df *.jsonb_agg would show what and where the other function is.

answered Aug 24, 2022 at 4:23

Adrian Klaver

20.4k3 gold badges24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Why Postgres jsonb_agg function is giving error operation & does not exist?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related