CODEWITHSUNDEEP
postgresqlspring-bootspring-data-jpahikaricp
comiventor
comiventor

Reputation: 4122

inserting json string from spring application into postgres jsonb

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.

Upvotes: 2

Views: 1782

Answers (1)

comiventor
comiventor

Reputation: 4122

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

Upvotes: 1

Related Questions