2 ms·
There are nicer ways to do batch insert in postgres. With 'jsob_to_recordset' there is only one parameter which is an array and it gets queried as a table. Tha
by furstenheim 6y ago
There are nicer ways to do batch insert in postgres.
With 'jsob_to_recordset' there is only one parameter which is an array and it gets queried as a table. That has the advantage of avoiding different queries for different inputs (it can be prepared only once, it's harder to commit mistakes...). Also it doesn't have the overhead of creating a huge string.
Another option is copy to. Using pg copy node https://github.com/brianc/node-pg-copy-streams https://github.com/brianc/node-pg-copy-streams you can insert/query as much as you want from/to node streams without memory footprint. I've used it to pipe millions of records straight to an http response (to download a csv)
- chinhodado 6y agoCan you elaborate please? I would like to learn more about this.
- furstenheim 6y agoHey, I just saw your comment. For the jsonb `INSERT INTO some_table (a, b) SELECT x.a, x.b FROM json_to_recordset('[{"a":1,"b":"foo"},{"a":"2","b":"bar"}]') AS x(a int, b text);` That one is super flexible and it can handle thousands of records. Where you have the array it would be $1 and pass it as a parameter. With streams it's trickier, probably you need to create a temporary table to pipe everything and then another query to insert where you want. Also you need to deal with async errors which is a pain. But you have 0 memory load. I've used it to insert/select millions of records ``` pool.connect(function(err, client, done) { var stream = client.query(copyFrom('COPY my_table FROM STDIN')); var fileStream = fs.createReadStream('some_file.tsv') fileStream.on('error', done); stream.on('error', done); stream.on('end', done); fileStream.pipe(stream); }); ```