Problems when updating Postgres JSON fields

Completed
Avatar
Permanently deleted user
Created

 Hi.

I have fields in my DB (PostgreSQL 9.5) with type JSON.

Often (but not always) attempt to update/insert (or just copy from another row) value in JSON field (from table view) leads to:

INSERT INTO "document"."types" ("id", "resultset", "name", "template", "for") VALUES (?, ?, ?, ?, ?);
[42804] ERROR: column "for" is of type json but expression is of type character varying
Hint: You will need to rewrite or cast the expression.

Am I doing wrong or missing something?

Any way to see how exactly DataGrip sending values to DB?

DataGrip 2017.2.2
Build #DB-172.4155.28, built on September 6, 2017

Votes

6

Share

5 comments
Avatar
vasily chernov
Created

Hi,

Could you provide you table DDL and some sample data to reproduce the problem?

Thank you.

0
Avatar
Permanently deleted user
Created

1.- Create a table as shown:

create table json_insert_failing (id serial primary key, data json);

2.- Open table in datagrip, insert a new row and write a json value into the field:

{"name": "sample"}

3.- Submit the change, an error is thrown:

[42804] ERROR: column "data" is of type json but expression is of type character varying Hint: You will need to rewrite or cast the expression. 

 

2
Avatar
Gustavo Andrade
Created

The same happened here:

1 -

CREATE TABLE orders (
ID serial NOT NULL PRIMARY KEY,
info json NOT NULL
);

2 - Works as expected
INSERT INTO orders (info)
VALUES
(
'{ "customer": "Lily Bush", "items": {"product": "Diaper","qty": 24}}'
),
(
'{ "customer": "Josh William", "items": {"product": "Toy Car","qty": 1}}'
),
(
'{ "customer": "Mary Clark", "items": {"product": "Toy Train","qty": 2}}'
);

3 - Manually add a json text -> 


 
I also tried to use single brackets around the json text but it fails the same way

EDIT: I am sorry. This problem happens to me in PHPStorm 2017.2.4. Only now i realized this is a DataGrip bug tracker.
 
0
Avatar
vasily chernov
Created

Hi Gustavo Andrade,

Fixed will be available in DataGrip 2017.3 and in PHPStorm 2017.3. By the way PHPStorm uses full DataGrip functionality.

Thank you.

0
Avatar
Permanently deleted user
Created

I solved by: Into the sql statement, I added this 'cast' where I have the json field:

INSERT INTO map_file(type, data)
VALUES (?, CAST(? AS json))
RETURNING id

 

the datatype of 'data' into map_file table is: json

0

Please sign in to leave a comment.