Postgres JSONB column insert error: invalid input syntax for type json the input string ended unexpectedly

Question

I'm trying to create a row manually via DBeaver and I am entering the following in a jsonb column:

{"US":"0.880","PA":"0.028","KY":"0.025"}

I've checked that this is valid JSON on https://jsonformatter.curiousconcept.com/#

However, this is what I get:

Any insight would be appreciated...

I even tried surrounding the object with single quotes like:

'{"US":"0.880","PA":"0.028","KY":"0.025"}'

But got an error about how the ' is an invalid token...

I was writing a nodejs script to insert a json stringified object into the column but I was getting the same error so I decided to manually try it and I can't even insert the above data...

Please add details to question, this works nicely on Postgres: select '{"US":"0.880","PA":"0.028","KY":"0.025"}'::jsonb;. (Using PostgreSQL 12.6 (Ubuntu 12.6-0ubuntu0.20.10.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 10.2.0-13ubuntu1) 10.2.0, 64-bit). — ariels - IGNORE AI
– ariels - IGNORE AI, Commented May 30, 2021 at 11:13
I'm adding the object manually via DBEAVER (DBMS) to the column... shouldn't it work? — Ayudh
– Ayudh, Commented May 30, 2021 at 11:18
Agree with @jjanes: This may be more an issue with DBEAVER than with PostgreSQL itself. — ariels - IGNORE AI
– ariels - IGNORE AI, Commented Jun 13, 2021 at 7:01

LonWo · Accepted Answer · 2021-06-01 08:51:53Z

2

I can insert this data in jsonb column in DBeaver via UI without problem.

But we have issue about input in json(not jsonb) column in table which has no primary key. Maybe is this your case https://github.com/dbeaver/dbeaver/issues/11704 ?

Or can you show table DDL?

insertjsonb

answered Jun 1, 2021 at 8:51

LonWo

4313 silver badges12 bronze badges

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

1 Comment

Ayudh Over a year ago

Hey, thanks for checking. I basically moved on. I still don't know why it wasn't working. It works if I insert it using an SQL query though. What I was doing is using the 'create column' functionality in the 'view table data' section

Robert · Accepted Answer · 2024-12-15 19:00:22Z

1

I have succeeded inserting your provided {"US":"0.880","PA":"0.028","KY":"0.025"} JSON into the JSONB column through the DBeaver table UI.

However, I had a similar problem with regularly formatted (indented) JSON. For me it worked to minify the JSON to a oneliner and try to insert that into the JSONB column. I used https://codebeautify.org/jsonminifier to do this.

I think the problem might be related to newlines / whitespace.

edited Dec 15, 2024 at 19:00

answered Dec 15, 2024 at 18:57

Robert

112 bronze badges

1 Comment

Ayudh Dec 17, 2024 at 9:08

I see, interesting find

Collectives™ on Stack Overflow

Postgres JSONB column insert error: invalid input syntax for type json the input string ended unexpectedly

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related