[racket] How to insert array into Postgresql using DB module

From: Ryan Culpepper (ryanc at ccs.neu.edu)
Date: Thu Jan 3 19:28:47 EST 2013

On 01/03/2013 05:08 PM, J G Cho wrote:
> Postgresql allows
>
> INSERT INTO sal_emp
>      VALUES ('Bill',
>      '{10000, 10000, 10000, 10000}',
>      '{{"meeting", "lunch"}, {"training", "presentation"}}');
>
> I have inherited this table that is not quite normalized and has
> columns that are expecting arrays.
>
> Can I do something like following?
>
> (query conn
>           "INSERT INTO sal_emp VALUES ($1, $2. $3)"
>          "Bill"
>          (list 10000 10000 10000 10000)
>          (list (list "meeting" "lunch") (list "training" "presentation")))

Yes, see the docs for pg-array. Your query would be written like this:

(query conn
        "INSERT INTO sal_emp VALUES ($1, $2, $3)"
        "Bill"
        (list 10000 10000 10000 10000)
        (pg-array 2 '(2 2) '(1 1)
                  (vector (vector "meeting" "lunch")
                          (vector "training" "presentation"))))

A list passed as a query parameter is automatically converted to a 
one-dimensional pg-array, but for two-dimensional arrays you'll have to 
construct a pg-array directly.

Ryan


Posted on the users mailing list.