Add option for batch insert

[Alkarex]

Based on https://node-postgres.com

There are a few different approaches to consider:

Copy streams: https://github.com/brianc/node-pg-copy-streams

Multiple executes:

CREATE TABLE person (
  id SERIAL PRIMARY KEY,
  name TEXT,
  age INTEGER
);

--

PREPARE insert_person_plan (text, integer) AS
INSERT INTO person (name, age)
VALUES ($1, $2);

BEGIN;
EXECUTE insert_person_plan('Alice', 25);
EXECUTE insert_person_plan('Bob', 30);
EXECUTE insert_person_plan('Charlie', 35);
COMMIT;

{
	"execute": {
		"insert_person_plan": [
			["Alice", 25],
			["Bob", 30],
			["Charlie", 35],
		]
	}
}

--

DEALLOCATE insert_person_plan;

SELECT * FROM person;

DROP TABLE person;

[thomasvnl]

Hi,

Would be real nice if it could work a bit like the batch node from https://flows.nodered.org/node/node-red-contrib-influxdb where you can give it an array of items to write. Knowing that PostgreSQL is not the same as SQL (but can be made a little bit more of the same with the TimescaleDB extension, hence why I came here to mention this way of working), this would handle a large batch of for instance measurements (of the same type and thus same field/columns) to insert really well.

[Alkarex]

@thomasvnl While waiting for something smarter, inserting an array of values is already possible with a little function.
See this example https://flows.nodered.org/flow/687918dd5cb66a3bfc2a661e15ef4237
This is very needed for good performance

[thomasvnl]

Yes, thank you. I did manage to do it this way already, indeed because of the better performance.

[regie-techno]

Hi,

It would be great to manage multiple queries with a simple array of queries !
Can you imagine it ?

[Alkarex]

@regie-techno Yes. For now, a little function to produce the array before the PostgreSQL node is the best option https://flows.nodered.org/flow/687918dd5cb66a3bfc2a661e15ef4237
But help welcome to propose a concept for a better batch insert