PostgreSQL: Insert into JSONB key failing with syntax error at or near “->>”

All we need is an easy explanation of the problem, so here it is.

Running the following query auto-generated by Eloquent’s upsert function is throwing a syntax error and I’m not sure why? I couldn’t find a supporting statement that says PostgreSQL supports the following syntax, looking for some expert advice on whether this would work.

insert into "plugin_positions" 
("created_at", "positions"->>"test", "slug", "tag", "updated_at") 
values 
('2021-04-10 17:30:40', 0, 'contact-for-telegram', 'rrss', '2021-04-10 17:30:40');

Here’s the query that works (which uses the simple column name, and a valid json value):

insert into "plugin_positions" 
("created_at", "positions", "slug", "tag", "updated_at") 
values 
('2021-04-10 17:30:40', '{"test":0}', 'contact-for-telegram', 'rrss', '2021-04-10 17:30:40');

Does postgresql allow inserting into a table if we specify the column as "positions"->>"test"?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

The reason is that the attributes of a JSON column are not database columns, so you cannot use them in the SET clause of an UPDATE statement.

You can only replace the complete JSON with an UPDATE statement.

It might be a good idea to use regular "scalar" database columns instead of lumping several attributes together into a JSON if you want to modify them individually.

Method 2

You can’t use JSON FUNCTIONS for purpose on that positioin.

At your position ("created_at", "positions"->>"test" it expects a column name.

You may have a problem with eloquent, so you should ask at SO

What you can do, after some thinking is something like

DO $$
BEGIN

EXECUTE CONCAT('insert into "plugin_positions" ("created_at",', "positions"->>"test" , E', "slug", "tag", "updated_at") VALUES  (''2021-04-10 17:30:40'', 0, ''contact-for-telegram'', ''rrss'', ''2021-04-10 17:30:40'');');
END $$;
   ;

This dynamic sql would make your insert, with the json "positions"

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply