This is basic to intermediate level tutorial configuration:- node-red, PostgreSQL and pgAdmin is installed on same Ubuntu machine. lots of tutorial available how to install PostgreSQL and pgAdmin. On node red some plant data is received on MQTT node as string. my goal is to log MQTT data to PostgreSQL with time stamp. so I can use it further for visualisation and generating reports. Step 1: - Install node-red-contrib-re-postgres. this can be installed with pallet easily. link for this node details https://flows.nodered.org/node/node-red-contrib-re-postgres Step 2:- Create database and tables in PostgresSQL by node red. database name = test and table name = production. As i am getting below data on msg.payload form MQTT {"timestamp":1589880187489,"values":[{"id":"nodered.packaging.speed1","v":45,"q":true,"t":1589880187258},{"id":"nodered.packaging.speed2","v":76,"q":true,"t":1589880187258},{"id":"nodered.packaging.speed3","v":325,"q":true,"t":1589880187258},{"id":"nodered.packaging.speed4","v":439,"q":true,"t":1589880187258},{"id":"nodered.packaging.speed5","v":32,"q":true,"t":1589880187258},{"id":"nodered.packaging.speed7","v":627,"q":true,"t":1589880187258},{"id":"nodered.packaging.speed8","v":97,"q":true,"t":1589880187258},{"id":"nodered.packaging.speed9","v":255,"q":true,"t":1589880187258}]} this message contains many details which I don't require so I want data in this format. long name is replaced by small name like name1, name2 etc. I will use local time of UBUNTU machine. msg.payload = {speed1:val1, speed2:val2, speed3:val3, speed4:val4, speed5:val5, speed6:val6, speed7:val7, speed8:val8}; for that I will use function node to manipulate payload data and construct new msg.payload as above format. CREATE TABLE public.production ( id serial PRIMARY KEY,times TIMESTAMP, speed1 integer,speed2 integer,speed3 integer,speed4 integer,speed5 integer,speed6 integer,speed7 integer,speed8 integer ) WITH ( OIDS=FALSE ); Step 3:- Log the data convert 1 function node code var test = msg.payload; var name1 = test.values[0].id; var val1 = test.values[0].v; var name2 = test.values[1].v; var val2 = test.values[1].v; var name3 = test.values[2].id; var val3 = test.values[2].v; var name4 = test.values[3].id; var val4 = test.values[3].v; var name5 = test.values[4].id; var val5 = test.values[4].v; var name6 = test.values[5].id; var val6 = test.values[5].v; var name7 = test.values[6].id; var val7 = test.values[6].v; var name8 = test.values[7].id; var val8 = test.values[7].v; msg.payload = "INSERT INTO public.production (speed1, speed2, speed3, speed4, speed5, speed6, speed7, speed8) VALUES (" +val1 +","+val2 +"," +val3 +"," +val4 +"," +val5 +"," +val6 +"," + val7 + "," +val8 + ");"; return msg; convert 1 function node code msg.queryParameters = msg.payload; return msg;
This is basic to intermediate level tutorial configuration:- node-red, PostgreSQL and pgAdmin is installed on same Ubuntu machine. lots of tutorial available how to install PostgreSQL and pgAdmin. On node red some plant data is received on MQTT node as string. my goal is to log MQTT data to PostgreSQL with time stamp. so I can use it further for visualisation and generating reports. Step 1: - Install node-red-contrib-re-postgres. this can be installed with pallet easily. link for this node details https://flows.nodered.org/node/node-red-contrib-re-postgres Step 2:- Create database and tables in PostgresSQL by node red. database name = test and table name = production. As i am getting below data on msg.payload form MQTT {"timestamp":1589880187489,"values":[{"id":"nodered.packaging.speed1","v":45,"q":true,"t":1589880187258},{"id":"nodered.packaging.speed2","v":76,"q":true,"t":1589880187258},{"id":"nodered.packaging.speed3","v":325,"q":true,"t":1589880187258},{"id":"nodered.packaging.speed4","v":439,"q":true,"t":1589880187258},{"id":"nodered.packaging.speed5","v":32,"q":true,"t":1589880187258},{"id":"nodered.packaging.speed7","v":627,"q":true,"t":1589880187258},{"id":"nodered.packaging.speed8","v":97,"q":true,"t":1589880187258},{"id":"nodered.packaging.speed9","v":255,"q":true,"t":1589880187258}]} this message contains many details which I don't require so I want data in this format. long name is replaced by small name like name1, name2 etc. I will use local time of UBUNTU machine. msg.payload = {speed1:val1, speed2:val2, speed3:val3, speed4:val4, speed5:val5, speed6:val6, speed7:val7, speed8:val8}; for that I will use function node to manipulate payload data and construct new msg.payload as above format. CREATE TABLE public.production ( id serial PRIMARY KEY,times TIMESTAMP, speed1 integer,speed2 integer,speed3 integer,speed4 integer,speed5 integer,speed6 integer,speed7 integer,speed8 integer ) WITH ( OIDS=FALSE ); Step 3:- Log the data convert 1 function node code var test = msg.payload; var name1 = test.values[0].id; var val1 = test.values[0].v; var name2 = test.values[1].v; var val2 = test.values[1].v; var name3 = test.values[2].id; var val3 = test.values[2].v; var name4 = test.values[3].id; var val4 = test.values[3].v; var name5 = test.values[4].id; var val5 = test.values[4].v; var name6 = test.values[5].id; var val6 = test.values[5].v; var name7 = test.values[6].id; var val7 = test.values[6].v; var name8 = test.values[7].id; var val8 = test.values[7].v; msg.payload = "INSERT INTO public.production (speed1, speed2, speed3, speed4, speed5, speed6, speed7, speed8) VALUES (" +val1 +","+val2 +"," +val3 +"," +val4 +"," +val5 +"," +val6 +"," + val7 + "," +val8 + ");"; return msg; convert 1 function node code msg.queryParameters = msg.payload; return msg;