node pg and postgres jsonb data type

The corner case

If you use node pg client to query postgres, it tries to map node data types to postgres data types and vice versa.Ofcourse to some extent.

From the docs

JavaScript has great support for JSON and node-postgres converts json/jsonb objects directly into their JavaScript object via JSON.parse. Likewise sending an object to the PostgreSQL server via a query from node-postgres, node-posgres will call JSON.stringify on your outbound value, automatically converting it to json for the server.

So ideally, you just pass in a js object and it just gets inserted into the jsonb column.But there is an edge case where this fails.

Now assume you pass in a json array as one of the query params to update a jsonb column in the db.

client.query('insert into users ( data ) values ( $1 )', [ [1,2,3] ], function( error ){
  /* ERROR */
});

It fails with error

{ [error: invalid input syntax for type json]

The node pg client by default converts javascript arrays to postgres arrays and hence the json array is parsed as postgres array and hence the failure.You can use JSON.stringify() to avoid the error.

P.S: https://github.com/brianc/node-postgres/issues/442