insert or update native javascript array doesn't work

[mren]

Hey folks,

It is currently not possible to pass an javascript array to pg.

If I have a table

CREATE TABLE array (
    a integer[]
);

i can't use this

connection.query 'INSERT INTO array (a) VALUES ($1)', [ [1,2,3] ]

I receive: 'error: array value must start with "{" or dimension information]

but have to do this

connection.query 'INSERT INTO array VALUES ($1)', [ "{#{[1,2,3].join ','}}" ]

It would be great if pg could auto convert javascript arrays to pg arrays.

What do you guys think?

[booo]

Have you seen the prepareValue function? https://github.com/brianc/node-postgres/blob/master/lib/utils.js#L98 Somehow it should be possible to overwrite this function and get the desired functionality.

[booo]

E.g. https://gist.github.com/4113898

[brianc]

yeah, that's there for exactly to let you override or extend the type coercion. One thing to note in your example, you'll want to pass an array as the first item of the params array:

client.query('INSERT INTO awesome_array_table(val) VALUES($1)', [ [1,2,3] ], ...)

[booo]

@brianc but how do I overwrite the prepareValue function if I don't want to do something like:

require('./node_modules/pg/lib/utils')

Where do we export the prepareValue function other than the utils module? Or what is the recommended way to overwrite the function?

[brianc]

Yeah, it's not currently exported on the main object returned from require('pg'). I suppose it could be exported there for easier overwriting. Right now you would have to do what you suggest with requiring the file directly & monkey-patching the function.

[mren]

thanks @booo for hinting at prepareValue

arrays are a base data type in postgres, are there any reasons not to make a pull request and add a conversion mechanism for arrays?

(i corrected my example code)

[brianc]

There's no reason not to. It's rather difficult to encode all the
different types of arrays properly. If you submit a pull request with
tests I'll almost always merge it happily and thankfully.

On Tue, Nov 20, 2012 at 2:10 AM, Mark Engel notifications@github.comwrote:

thanks @booo https://github.com/booo for hinting at prepareValue

arrays are a base data type in postgres, are there any reasons not to make
a pull request and add a conversion mechanism for arrays?

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/220#issuecomment-10545786.

[mren]

back from holiday :)

it would be awesome if node-pg could translate all different types of arrays, for sure.
would it be ok, if we start with integers and then move on to the next type as we encounter them and submit another pull request?

[brianc]

absolutely.

On Wed, Jan 9, 2013 at 6:05 AM, Mark Engel notifications@github.com wrote:

back from holiday :)

it would be awesome if node-pg could translate all different types of
arrays, for sure.
would it be ok, if we start with integers and then move on to the next
type as we encounter them and submit another pull request?

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/220#issuecomment-12041332.

[brianc]

Closing. Awaiting pull request(s) for array types.