Properly crafting the msg.params for postgresql

I'm trying to import a considerable amount of XML data into a TimescaleDB in Postgresql. I'm using the node-red-contrib-postgresql node. I've got it to the point where the data has the correct columns assigned to it, but I've been banging my head against the wall trying to get the msg.params setup properly to get it into the database.

Without the function trying to define msg.params, I predictably get the params not being defined:
error: there is no parameter $1 INSERT INTO power_data (time, value, production_type) VALUES ($1, $2, $3);

With this function defined:

// Initialize an array to store the flattened message parameters
msg.params = [];

// Iterate over each entry in the payload array
msg.payload.forEach(function(entry) {
    // Extract timestamp, value, and production type from the entry
    var time = entry.time;
    var value = entry.value;
    var productionType = entry.production_type;
    
    // Construct an array of parameters for the current row
    var rowParams = [time, value, productionType];
    
    // Push the parameters of the current row to the flattened message parameters array
    msg.params.push(rowParams);
});

// Log the message parameters array for debugging
console.log("Message parameters: ", msg.params);

// Return the message
return msg;

I get the following error:
error: bind message supplies 6192 parameters, but prepared statement "" requires 3 INSERT INTO power_data (time, value, production_type) VALUES ($1, $2, $3);

I am VERY new to this, so please go easy!

I think the problem is that you are constructing an array of arrays. Have you tried something like this?

[{"id":"a25a323e18db819d","type":"inject","z":"df9ca1cedaf4b0c2","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":180,"y":220,"wires":[["49e029f672733a50"]]},{"id":"49e029f672733a50","type":"function","z":"df9ca1cedaf4b0c2","name":"pre processing","func":"msg.payload = [{\"time\":1,\"value\":2,\"production_type\":3},\n{\"time\":2,\"value\":5,\"production_type\":2}];\nreturn msg;","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":360,"y":220,"wires":[["d56b55dffc4ccfb0"]]},{"id":"d56b55dffc4ccfb0","type":"split","z":"df9ca1cedaf4b0c2","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":530,"y":220,"wires":[["22caac947bd76f4d"]]},{"id":"96f90dee1d7ab7f2","type":"debug","z":"df9ca1cedaf4b0c2","name":"feed db","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":900,"y":220,"wires":[]},{"id":"22caac947bd76f4d","type":"function","z":"df9ca1cedaf4b0c2","name":"convert to array","func":"msg.payload = Object.values(msg.payload);\nreturn msg;","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":700,"y":220,"wires":[["96f90dee1d7ab7f2"]]}]

Sorry, I can't test this with a real db atm.

EDIT: I am sure there are better ways to do this .. but maybe it might help to get started.

As per the readme, params should be an array

    // Construct an array of parameters
    msg.params = [time, value, productionType];

I ended up solving it by using a split function. Probably not the most efficient method, but it did work and data is flowing into the database now.

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.