Inserting data into postgresql json columns using clojure.java.jdbc

Question

I'm trying to do an insert into a json column in a postgresql database using clojure.java.jdbc/insert!. I'm not sure which data format should be used when inserting json.

Table definition:

CREATE TABLE errors (
    id character varying(24) NOT NULL PRIMARY KEY,
    body json NOT NULL
);

Trying to use literal data in a map format:

=> (insert! db :errors {:id "a" :body {:message "A error"}}
                       {:id "b" :body {:message "B error"}})
PSQLException No hstore extension installed.  org.postgresql.jdbc2.AbstractJdbc2Statement.setMap (AbstractJdbc2Statement.java:1709)

Alternatively as a json encoded string:

=> (insert! db :errors {:id "a" :body "{\"message\":\"A error\"}"}
                       {:id "b" :body "{\"message\":\"B error\"}"})
PSQLException ERROR: column "body" is of type json but expression is of type character varying
  Hint: You will need to rewrite or cast the expression.
  Position: 46  org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse (QueryExecutorImpl.java:2198)

There doesn't seem to be an obvious way to do this. Of particular importance is that I need to be able to insert many records in a single query instead of one by one, a convenience insert! is providing for me.

What is a simple way to insert several records into a postgres table having a json column using clojure.java.jdbc?

Answer 1

Since clojure.java.jdbc exposes a few protocols, including clojure.java.jdbc/ISQLValue, you can extend it to allow supplying json as an ordinary clojure map. Travis Vachon provides a thorough explanation of this process and a code snippet implementing it.

I also found a library, clj-postgresql, which implements lots of extra postgres functionality, including json and jsonb data types. You can simply require clj-postgresql.types and json support will be imported.