-
-
Save notenoughtech/d2c3a8923359e5f33f222d591a40d560 to your computer and use it in GitHub Desktop.
PostgreSQL in NodeRED
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
[ | |
{ | |
"id": "7e4ebd722824573c", | |
"type": "postgresql", | |
"z": "18167fd57fe3ce43", | |
"name": "Create table", | |
"query": "CREATE TABLE thermaltest (\n id bigint PRIMARY KEY,\n device varchar(255) NOT NULL, \n test_type varchar(255) NOT NULL, \n temperature numeric[],\n timing int[], \n date DATE,\n fan boolean,\n ambient boolean,\n ambient_temperature numeric,\n sbc varchar(255),\n average_temp numeric\n);\n\n", | |
"postgreSQLConfig": "501b346caeb59359", | |
"split": false, | |
"rowsPerMsg": 1, | |
"outputs": 1, | |
"x": 750, | |
"y": 100, | |
"wires": [ | |
[ | |
"adf5c5247bd1dcfd" | |
] | |
] | |
}, | |
{ | |
"id": "edc40afe668f9265", | |
"type": "postgresql", | |
"z": "18167fd57fe3ce43", | |
"name": "Drop table", | |
"query": "DROP TABLE thermaltest;", | |
"postgreSQLConfig": "501b346caeb59359", | |
"split": false, | |
"rowsPerMsg": 1, | |
"outputs": 1, | |
"x": 750, | |
"y": 180, | |
"wires": [ | |
[ | |
"adf5c5247bd1dcfd" | |
] | |
] | |
}, | |
{ | |
"id": "711b883d2c811b5f", | |
"type": "inject", | |
"z": "18167fd57fe3ce43", | |
"name": "Remove", | |
"props": [ | |
{ | |
"p": "payload" | |
}, | |
{ | |
"p": "topic", | |
"vt": "str" | |
} | |
], | |
"repeat": "", | |
"crontab": "", | |
"once": false, | |
"onceDelay": 0.1, | |
"topic": "", | |
"payload": "", | |
"payloadType": "date", | |
"x": 300, | |
"y": 180, | |
"wires": [ | |
[ | |
"edc40afe668f9265" | |
] | |
] | |
}, | |
{ | |
"id": "2aabab04f4ce5936", | |
"type": "inject", | |
"z": "18167fd57fe3ce43", | |
"name": "Set", | |
"props": [ | |
{ | |
"p": "payload" | |
}, | |
{ | |
"p": "topic", | |
"vt": "str" | |
} | |
], | |
"repeat": "", | |
"crontab": "", | |
"once": false, | |
"onceDelay": 0.1, | |
"topic": "", | |
"payload": "", | |
"payloadType": "date", | |
"x": 290, | |
"y": 100, | |
"wires": [ | |
[ | |
"7e4ebd722824573c" | |
] | |
] | |
}, | |
{ | |
"id": "555db40438834f5c", | |
"type": "postgresql", | |
"z": "18167fd57fe3ce43", | |
"name": "Update Table", | |
"query": "UPDATE thermaltest \nSET temperature = array_append(temperature, $2),\n timing = array_append(timing, $3) \nWHERE id = $1;\n\n", | |
"postgreSQLConfig": "501b346caeb59359", | |
"split": false, | |
"rowsPerMsg": 1, | |
"outputs": 1, | |
"x": 750, | |
"y": 480, | |
"wires": [ | |
[ | |
"adf5c5247bd1dcfd" | |
] | |
] | |
}, | |
{ | |
"id": "a68dcbfb558e2417", | |
"type": "postgresql", | |
"z": "18167fd57fe3ce43", | |
"name": "Insert results", | |
"query": "INSERT INTO thermaltest (id, temperature, timing, device, test_type)\n VALUES ($1, $2, $3, $4, $5);\n\n\n\n \n", | |
"postgreSQLConfig": "501b346caeb59359", | |
"split": false, | |
"rowsPerMsg": 1, | |
"outputs": 1, | |
"x": 750, | |
"y": 320, | |
"wires": [ | |
[ | |
"adf5c5247bd1dcfd" | |
] | |
] | |
}, | |
{ | |
"id": "8e7171e9279e2876", | |
"type": "postgresql", | |
"z": "18167fd57fe3ce43", | |
"name": "Delete Row", | |
"query": "DELETE FROM thermaltest WHERE id = $1;", | |
"postgreSQLConfig": "501b346caeb59359", | |
"split": false, | |
"rowsPerMsg": 1, | |
"outputs": 1, | |
"x": 750, | |
"y": 860, | |
"wires": [ | |
[ | |
"adf5c5247bd1dcfd" | |
] | |
] | |
}, | |
{ | |
"id": "7e423920f8127887", | |
"type": "postgresql", | |
"z": "18167fd57fe3ce43", | |
"name": "Read data", | |
"query": "SELECT id, device\nFROM thermaltest;", | |
"postgreSQLConfig": "501b346caeb59359", | |
"split": false, | |
"rowsPerMsg": 1, | |
"outputs": 1, | |
"x": 750, | |
"y": 620, | |
"wires": [ | |
[ | |
"adf5c5247bd1dcfd" | |
] | |
] | |
}, | |
{ | |
"id": "f3b5b5c4dfbc13fc", | |
"type": "debug", | |
"z": "18167fd57fe3ce43", | |
"name": "debug 26", | |
"active": true, | |
"tosidebar": true, | |
"console": false, | |
"tostatus": false, | |
"complete": "false", | |
"statusVal": "", | |
"statusType": "auto", | |
"x": 1080, | |
"y": 500, | |
"wires": [] | |
}, | |
{ | |
"id": "edc590e51655c299", | |
"type": "function", | |
"z": "18167fd57fe3ce43", | |
"name": "Set data", | |
"func": "var id = 1235;\nvar temperature = [39, 39, 40, 40];\nvar timing = [0, 2, 3, 4];\n\nvar device = \"sample device\";\nvar test_type = \"idle\";\n\n\nmsg.params = [id, temperature, timing, device, test_type];\n\nreturn msg;", | |
"outputs": 1, | |
"noerr": 0, | |
"initialize": "", | |
"finalize": "", | |
"libs": [], | |
"x": 480, | |
"y": 320, | |
"wires": [ | |
[ | |
"a68dcbfb558e2417" | |
] | |
] | |
}, | |
{ | |
"id": "57ba55f3d0b26982", | |
"type": "comment", | |
"z": "18167fd57fe3ce43", | |
"name": "Create Table Manually", | |
"info": "", | |
"x": 480, | |
"y": 80, | |
"wires": [] | |
}, | |
{ | |
"id": "2b922519686714df", | |
"type": "comment", | |
"z": "18167fd57fe3ce43", | |
"name": "Delete Table Manually", | |
"info": "", | |
"x": 480, | |
"y": 160, | |
"wires": [] | |
}, | |
{ | |
"id": "1fa204e844cafb54", | |
"type": "postgresql", | |
"z": "18167fd57fe3ce43", | |
"name": "Insert results", | |
"query": "INSERT INTO thermaltest (id, device, test_type, temperature, timing, date, fan, ambient_temperature, ambient, sbc, average_temp)\n VALUES (1233, 'my device', 'idle', ARRAY[39, 39, 40, 40], ARRAY[0, 2, 3, 4], '2020-05-12T23:50:21.817Z', true, 21, true, 'board', 44);\n\n\n\n \n", | |
"postgreSQLConfig": "501b346caeb59359", | |
"split": false, | |
"rowsPerMsg": 1, | |
"outputs": 1, | |
"x": 750, | |
"y": 240, | |
"wires": [ | |
[ | |
"adf5c5247bd1dcfd" | |
] | |
] | |
}, | |
{ | |
"id": "4a7e55522bd69874", | |
"type": "inject", | |
"z": "18167fd57fe3ce43", | |
"name": "set", | |
"props": [ | |
{ | |
"p": "payload" | |
}, | |
{ | |
"p": "topic", | |
"vt": "str" | |
} | |
], | |
"repeat": "", | |
"crontab": "", | |
"once": false, | |
"onceDelay": 0.1, | |
"topic": "", | |
"payload": "", | |
"payloadType": "date", | |
"x": 290, | |
"y": 320, | |
"wires": [ | |
[ | |
"edc590e51655c299" | |
] | |
] | |
}, | |
{ | |
"id": "d0345af537bb0fe0", | |
"type": "comment", | |
"z": "18167fd57fe3ce43", | |
"name": "Set Row Dynamically", | |
"info": "", | |
"x": 480, | |
"y": 280, | |
"wires": [] | |
}, | |
{ | |
"id": "d994e49650b7f575", | |
"type": "inject", | |
"z": "18167fd57fe3ce43", | |
"name": "Set", | |
"props": [ | |
{ | |
"p": "payload" | |
}, | |
{ | |
"p": "topic", | |
"vt": "str" | |
} | |
], | |
"repeat": "", | |
"crontab": "", | |
"once": false, | |
"onceDelay": 0.1, | |
"topic": "", | |
"payload": "", | |
"payloadType": "date", | |
"x": 290, | |
"y": 240, | |
"wires": [ | |
[ | |
"1fa204e844cafb54" | |
] | |
] | |
}, | |
{ | |
"id": "0664f8e8cbf67200", | |
"type": "comment", | |
"z": "18167fd57fe3ce43", | |
"name": "Set Row Manually", | |
"info": "", | |
"x": 470, | |
"y": 220, | |
"wires": [] | |
}, | |
{ | |
"id": "97c8139e3b56842a", | |
"type": "function", | |
"z": "18167fd57fe3ce43", | |
"name": "Set data", | |
"func": "var id = 1233;\nvar temperature = 21;\nvar timing = 6;\n\nmsg.params = [id, temperature, timing];\n\nreturn msg;", | |
"outputs": 1, | |
"noerr": 0, | |
"initialize": "", | |
"finalize": "", | |
"libs": [], | |
"x": 480, | |
"y": 480, | |
"wires": [ | |
[ | |
"555db40438834f5c" | |
] | |
] | |
}, | |
{ | |
"id": "1284f2d9dafc7c63", | |
"type": "inject", | |
"z": "18167fd57fe3ce43", | |
"name": "set", | |
"props": [ | |
{ | |
"p": "payload" | |
}, | |
{ | |
"p": "topic", | |
"vt": "str" | |
} | |
], | |
"repeat": "", | |
"crontab": "", | |
"once": false, | |
"onceDelay": 0.1, | |
"topic": "", | |
"payload": "", | |
"payloadType": "date", | |
"x": 290, | |
"y": 480, | |
"wires": [ | |
[ | |
"97c8139e3b56842a" | |
] | |
] | |
}, | |
{ | |
"id": "e662285b6083b4b1", | |
"type": "comment", | |
"z": "18167fd57fe3ce43", | |
"name": "Update Row Dynamically", | |
"info": "", | |
"x": 490, | |
"y": 440, | |
"wires": [] | |
}, | |
{ | |
"id": "ade0bf484e61e6cb", | |
"type": "postgresql", | |
"z": "18167fd57fe3ce43", | |
"name": "Update Table", | |
"query": "UPDATE thermaltest\nSET temperature = array_append(temperature, 44),\n timing = array_append(timing, 66),\n device_name = \"new name\"\n\nWHERE id = 1233;", | |
"postgreSQLConfig": "501b346caeb59359", | |
"split": false, | |
"rowsPerMsg": 1, | |
"outputs": 1, | |
"x": 750, | |
"y": 400, | |
"wires": [ | |
[ | |
"adf5c5247bd1dcfd" | |
] | |
] | |
}, | |
{ | |
"id": "c6773af94dfeb426", | |
"type": "inject", | |
"z": "18167fd57fe3ce43", | |
"name": "set", | |
"props": [ | |
{ | |
"p": "payload" | |
}, | |
{ | |
"p": "topic", | |
"vt": "str" | |
} | |
], | |
"repeat": "", | |
"crontab": "", | |
"once": false, | |
"onceDelay": 0.1, | |
"topic": "", | |
"payload": "", | |
"payloadType": "date", | |
"x": 290, | |
"y": 400, | |
"wires": [ | |
[ | |
"ade0bf484e61e6cb" | |
] | |
] | |
}, | |
{ | |
"id": "434985242b82d648", | |
"type": "comment", | |
"z": "18167fd57fe3ce43", | |
"name": "Update Row Manually", | |
"info": "", | |
"x": 480, | |
"y": 380, | |
"wires": [] | |
}, | |
{ | |
"id": "d04512103d6a65a9", | |
"type": "postgresql", | |
"z": "18167fd57fe3ce43", | |
"name": "Read All data", | |
"query": "SELECT * FROM thermaltest;", | |
"postgreSQLConfig": "501b346caeb59359", | |
"split": false, | |
"rowsPerMsg": 1, | |
"outputs": 1, | |
"x": 750, | |
"y": 560, | |
"wires": [ | |
[ | |
"adf5c5247bd1dcfd" | |
] | |
] | |
}, | |
{ | |
"id": "680e8cd134d9d166", | |
"type": "inject", | |
"z": "18167fd57fe3ce43", | |
"name": "set", | |
"props": [ | |
{ | |
"p": "payload" | |
}, | |
{ | |
"p": "topic", | |
"vt": "str" | |
} | |
], | |
"repeat": "", | |
"crontab": "", | |
"once": false, | |
"onceDelay": 0.1, | |
"topic": "", | |
"payload": "", | |
"payloadType": "date", | |
"x": 290, | |
"y": 560, | |
"wires": [ | |
[ | |
"d04512103d6a65a9" | |
] | |
] | |
}, | |
{ | |
"id": "decd2a16f40002af", | |
"type": "inject", | |
"z": "18167fd57fe3ce43", | |
"name": "set", | |
"props": [ | |
{ | |
"p": "payload" | |
}, | |
{ | |
"p": "topic", | |
"vt": "str" | |
} | |
], | |
"repeat": "", | |
"crontab": "", | |
"once": false, | |
"onceDelay": 0.1, | |
"topic": "", | |
"payload": "", | |
"payloadType": "date", | |
"x": 290, | |
"y": 620, | |
"wires": [ | |
[ | |
"7e423920f8127887" | |
] | |
] | |
}, | |
{ | |
"id": "df89436018e8e092", | |
"type": "comment", | |
"z": "18167fd57fe3ce43", | |
"name": "Read all", | |
"info": "", | |
"x": 440, | |
"y": 540, | |
"wires": [] | |
}, | |
{ | |
"id": "a2c431ec3e90be2a", | |
"type": "comment", | |
"z": "18167fd57fe3ce43", | |
"name": "Read selected columns", | |
"info": "", | |
"x": 480, | |
"y": 600, | |
"wires": [] | |
}, | |
{ | |
"id": "928ca92d54ea7447", | |
"type": "postgresql", | |
"z": "18167fd57fe3ce43", | |
"name": "Read data", | |
"query": "SELECT id, device, test_type, fan, average_temp FROM thermaltest ORDER BY average_temp LIMIT 10", | |
"postgreSQLConfig": "501b346caeb59359", | |
"split": false, | |
"rowsPerMsg": 1, | |
"outputs": 1, | |
"x": 750, | |
"y": 680, | |
"wires": [ | |
[ | |
"adf5c5247bd1dcfd" | |
] | |
] | |
}, | |
{ | |
"id": "c87c1e0ab08bc139", | |
"type": "postgresql", | |
"z": "18167fd57fe3ce43", | |
"name": "Read data", | |
"query": "SELECT * FROM thermaltest WHERE id = $1;", | |
"postgreSQLConfig": "501b346caeb59359", | |
"split": false, | |
"rowsPerMsg": 1, | |
"outputs": 1, | |
"x": 750, | |
"y": 760, | |
"wires": [ | |
[ | |
"adf5c5247bd1dcfd" | |
] | |
] | |
}, | |
{ | |
"id": "6301f4a12321a4d1", | |
"type": "inject", | |
"z": "18167fd57fe3ce43", | |
"name": "set", | |
"props": [ | |
{ | |
"p": "payload" | |
}, | |
{ | |
"p": "topic", | |
"vt": "str" | |
} | |
], | |
"repeat": "", | |
"crontab": "", | |
"once": false, | |
"onceDelay": 0.1, | |
"topic": "", | |
"payload": "", | |
"payloadType": "date", | |
"x": 290, | |
"y": 680, | |
"wires": [ | |
[ | |
"928ca92d54ea7447" | |
] | |
] | |
}, | |
{ | |
"id": "daa950bdb9945c5c", | |
"type": "inject", | |
"z": "18167fd57fe3ce43", | |
"name": "set", | |
"props": [ | |
{ | |
"p": "payload" | |
}, | |
{ | |
"p": "topic", | |
"vt": "str" | |
} | |
], | |
"repeat": "", | |
"crontab": "", | |
"once": false, | |
"onceDelay": 0.1, | |
"topic": "", | |
"payload": "", | |
"payloadType": "date", | |
"x": 290, | |
"y": 760, | |
"wires": [ | |
[ | |
"7f00675163506224" | |
] | |
] | |
}, | |
{ | |
"id": "177bf238214bc730", | |
"type": "inject", | |
"z": "18167fd57fe3ce43", | |
"name": "set", | |
"props": [ | |
{ | |
"p": "payload" | |
}, | |
{ | |
"p": "topic", | |
"vt": "str" | |
} | |
], | |
"repeat": "", | |
"crontab": "", | |
"once": false, | |
"onceDelay": 0.1, | |
"topic": "", | |
"payload": "", | |
"payloadType": "date", | |
"x": 290, | |
"y": 860, | |
"wires": [ | |
[ | |
"0fee4bdff5936104" | |
] | |
] | |
}, | |
{ | |
"id": "0fee4bdff5936104", | |
"type": "function", | |
"z": "18167fd57fe3ce43", | |
"name": "Set data", | |
"func": "var id = 1233;\n\n\nmsg.params = [id];\n\nreturn msg;", | |
"outputs": 1, | |
"noerr": 0, | |
"initialize": "", | |
"finalize": "", | |
"libs": [], | |
"x": 480, | |
"y": 860, | |
"wires": [ | |
[ | |
"8e7171e9279e2876" | |
] | |
] | |
}, | |
{ | |
"id": "e7c866cb48817715", | |
"type": "comment", | |
"z": "18167fd57fe3ce43", | |
"name": "Delete row", | |
"info": "", | |
"x": 440, | |
"y": 820, | |
"wires": [] | |
}, | |
{ | |
"id": "302c38f3cb54aedc", | |
"type": "comment", | |
"z": "18167fd57fe3ce43", | |
"name": "Read 10 based on top average temp", | |
"info": "", | |
"x": 520, | |
"y": 660, | |
"wires": [] | |
}, | |
{ | |
"id": "7f00675163506224", | |
"type": "function", | |
"z": "18167fd57fe3ce43", | |
"name": "Set data", | |
"func": "var id = 1233;\n\nmsg.params = [id];\n\nreturn msg;", | |
"outputs": 1, | |
"noerr": 0, | |
"initialize": "", | |
"finalize": "", | |
"libs": [], | |
"x": 480, | |
"y": 760, | |
"wires": [ | |
[ | |
"c87c1e0ab08bc139" | |
] | |
] | |
}, | |
{ | |
"id": "16fd13af4b7584f1", | |
"type": "comment", | |
"z": "18167fd57fe3ce43", | |
"name": "Read selected row", | |
"info": "", | |
"x": 470, | |
"y": 720, | |
"wires": [] | |
}, | |
{ | |
"id": "42f7cdf42f65f296", | |
"type": "comment", | |
"z": "18167fd57fe3ce43", | |
"name": "Limitation", | |
"info": "Hardcoded numbers passed as int to array, \nwhich may not be the same datatype", | |
"x": 140, | |
"y": 400, | |
"wires": [] | |
}, | |
{ | |
"id": "4017f4665e8b1d59", | |
"type": "comment", | |
"z": "18167fd57fe3ce43", | |
"name": "Limitation - configuration", | |
"info": "Careful, when exported passwords for \npostgres configurations are stored as plain\ntext. See expported JSON file.", | |
"x": 1030, | |
"y": 60, | |
"wires": [] | |
}, | |
{ | |
"id": "adf5c5247bd1dcfd", | |
"type": "junction", | |
"z": "18167fd57fe3ce43", | |
"x": 960, | |
"y": 500, | |
"wires": [ | |
[ | |
"f3b5b5c4dfbc13fc" | |
] | |
] | |
}, | |
{ | |
"id": "501b346caeb59359", | |
"type": "postgreSQLConfig", | |
"name": "Test DB", | |
"host": "127.0.0.1", | |
"hostFieldType": "str", | |
"port": "5432", | |
"portFieldType": "num", | |
"database": "thermaltest", | |
"databaseFieldType": "str", | |
"ssl": "false", | |
"sslFieldType": "bool", | |
"applicationName": "", | |
"applicationNameType": "str", | |
"max": "10", | |
"maxFieldType": "num", | |
"idle": "1000", | |
"idleFieldType": "num", | |
"connectionTimeout": "10000", | |
"connectionTimeoutFieldType": "num", | |
"user": "pi", | |
"userFieldType": "str", | |
"password": "thisisnotmypassword", | |
"passwordFieldType": "str" | |
} | |
] |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment