Skip to content

Instantly share code, notes, and snippets.

@notenoughtech
Created January 25, 2023 19:19
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save notenoughtech/d2c3a8923359e5f33f222d591a40d560 to your computer and use it in GitHub Desktop.
Save notenoughtech/d2c3a8923359e5f33f222d591a40d560 to your computer and use it in GitHub Desktop.
PostgreSQL in NodeRED
[
{
"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