Join the Stack Overflow Community
Stack Overflow is a community of 6.8 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up

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?

share|improve this question
up vote 5 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.