Dealing with PostgreSQL Arrays in Clojure
One of the things I want to use a little more in PostgreSQL is the ARRAY datatype. It's pretty simple to imagine why it's a good thing -- face it, I can have the data flattened out:
CREATE TABLE seasonality ( id uuid PRIMARY KEY DEFAULT uuid_generate_v4(), service VARCHAR, jan INTEGER, feb INTEGER, mar INTEGER, apr INTEGER, may INTEGER, jun INTEGER, jul INTEGER, aug INTEGER, sep INTEGER, oct INTEGER, nov INTEGER, DEC INTEGER )
or we can put it in an array, and it's so much cleaner to deal with:
CREATE TABLE seasonality ( id uuid PRIMARY KEY DEFAULT uuid_generate_v4(), service VARCHAR, factor_by_month INTEGER[12] )
The problem is that when dealing with Korma, it doesn't like the array datatype as it's coming back as a Jdbc4Array and not something clojure can easily compare to a standard array of integers.
So what's a guy to do? Well… it's a pretty simple conversion:
(defn extract-factors [record] (-> record :factor_by_month .getArray seq))
and the output of this is easily comparable to a simple array of integers.
Not bad. I spent a little time on this, and StackOverflow helped, but clojure isn't too bad at this. A lot more like erlang.
UPDATE: a co-worker pointed out that I could really make this a lot nicer, and I have to agree. The first thing is to make the function just convert the Jdbc4Array to a sequence:
(defn array->seq [array] (-> array .getArray seq))
then we can make use of Korma's transform method on the entity to transform the data on the select calls:
(defentity seasonality (transform (fn [rec] (update-in rec [:factor_by_month] array->seq)))
Then, as soon as we see the data from the select, it's already converted to a sequence and we don't have to worry about it. Works great, and really makes things cleaner.