2 ms·
Hey, 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
by furstenheim 6y ago
Hey, 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);
});
```