inserting json string from spring application into postgres jsonb

Question

I want to insert json string, which has no predefined structure i.e. key value formats are not fixed, into a postgres column with jsonb datatype. My application uses HikariCP alongwith JPA.

Earlier I had the following generic cast operation in postgres to achieve the above but its not recommended by my DBA as it fundamentally alters the varchar datatype.

CREATE CAST (character varying AS jsonb) WITH INOUT AS IMPLICIT;

While looking for application side alternatives, I came across setting stringtype as unspecified like below

spring:   
  profiles: development   
  datasource:
    type: com.zaxxer.hikari.HikariDataSource
    driver-class-name: org.postgresql.Driver
    url: jdbc:postgresql://127.0.0.1/mydb
    username: my_user
    password: 'my_password'
    hikari:
      auto-commit: true
      maximumPoolSize: 2
      leakDetectionThreshold: 60000
    tomcat:
      connection-properties: stringtype=unspecified

The above configuration doesn't help. connection-properties doesn't seem to be a direct property of datasource. Various answers on forums just tell you to set stringtype=unspecified in connection-properties but a clear working answer would be of more help.

comiventor · Accepted Answer · 2017-11-29 10:45:55Z

1

While thinking about it, I realized that this property must be set HikariCP. When I looked through various parameters it allows me to set, I came across data-source-properties. Following is what I discovered as the solution to above problem

hikari:
  data-source-properties: stringtype=unspecified

answered Nov 29, 2017 at 10:45

comiventor

4,1725 gold badges59 silver badges80 bronze badges

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

2 Comments

abbas Over a year ago

For those who are looking for the flattened version spring.datasource.hikari.data-source-properties.stringtype=unspecified

Oleksandr Yefymov Over a year ago

this is good for json, but for me breaks LIKE query with emty parameters as side effect

Collectives™ on Stack Overflow

inserting json string from spring application into postgres jsonb

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related