PostgreSQL数据库—NodeRED连接示例

提示💡：下面所有操作，都是已经安装好PostgreSQL数据库的情况下演示操作；具体，如何安装数据库，这里不说明。

1、准备工作 #

1-1、创建数据库名【aiotedge】 #

1-2、执行表结构文件 #

下面为示例数据库的表结构:

DROP TABLE IF EXISTS "public"."device_data";
CREATE TABLE "public"."device_data" (
  "id" int4 NOT NULL,
  "attribute_name" varchar(50) COLLATE "pg_catalog"."default",
  "attribute_value" float8,
  "create_time" timestamp(0)
)
;
COMMENT ON COLUMN "public"."device_data"."id" IS 'ID';
COMMENT ON COLUMN "public"."device_data"."attribute_name" IS '属性名称';
COMMENT ON COLUMN "public"."device_data"."attribute_value" IS '属性值';

-- ----------------------------
-- Primary Key structure for table device_data
-- ----------------------------
ALTER TABLE "public"."device_data" ADD CONSTRAINT "deviceData_pkey" PRIMARY KEY ("id");

2、PostgreSQL数据库常规操作 #

2-1、示例代码 #

[{"id":"e1390262b64eaea2","type":"function","z":"cb5f1a5e643cfd9e","name":"INSERT Data【插入数据】","func":"\nvar datetime = formatDateTime();//创建日期格式的时间\n\nvar minBound = 15, maxBound = 25;//创建温度模拟数据15~25\nvar randomNum = Math.random() * (maxBound - minBound) + minBound;\nvar wendu = randomNum.toFixed(2);\n\nvar id = \"1\";\nvar attributeName = \"温度\";\n\nmsg.query = \"INSERT INTO device_data (id,attribute_name,attribute_value,create_time) VALUES \"\n    + \"('\" + id + \"','\" + attributeName + \"','\" + wendu + \"','\" + datetime +\"')\";\n\nreturn msg;\n\n//得到当前日期 【YYYY-MM-DD HH:mm:ss】\nfunction formatDateTime() {\n    var now = new Date();\n    var year = now.getFullYear(); // 获取年份\n    var month = now.getMonth() + 1; // 获取月份，月份从0开始，所以需要+1\n    var day = now.getDate(); // 获取日期\n    var hours = now.getHours(); // 获取小时\n    var minutes = now.getMinutes(); // 获取分钟\n    var seconds = now.getSeconds(); // 获取秒数\n\n    // 将单个数字格式化为两位数（例如，1变为01）\n    month = month < 10 ? '0' + month : month;\n    day = day < 10 ? '0' + day : day;\n    hours = hours < 10 ? '0' + hours : hours;\n    minutes = minutes < 10 ? '0' + minutes : minutes;\n    seconds = seconds < 10 ? '0' + seconds : seconds;\n\n    // 拼接成最终的字符串\n    var dateTimeStr = year + '-' + month + '-' + day + ' ' + hours + ':' + minutes + ':' + seconds;\n    return dateTimeStr;\n}\n","outputs":1,"timeout":"","noerr":0,"initialize":"","finalize":"","libs":[],"x":280,"y":400,"wires":[["c5f0738fc74b375b","bbec5b56a85c8470"]]},{"id":"d45a48775b35da17","type":"inject","z":"cb5f1a5e643cfd9e","name":"Go","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":"","topic":"","payloadType":"date","x":90,"y":400,"wires":[["e1390262b64eaea2"]]},{"id":"861bc8f9d83a66d0","type":"inject","z":"cb5f1a5e643cfd9e","name":"Go","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":"","topic":"","payloadType":"date","x":90,"y":460,"wires":[["b4fcb51aa1b56f1d"]]},{"id":"b4fcb51aa1b56f1d","type":"function","z":"cb5f1a5e643cfd9e","name":"Update Data【更新数据】","func":"var value = \"22.55\";\nvar id = \"1\";\n\nmsg.query = \"UPDATE device_data SET attribute_value = '\" + value + \"' WHERE id='\" + id +\"'\";\n\nreturn msg;","outputs":1,"timeout":"","noerr":0,"initialize":"","finalize":"","libs":[],"x":270,"y":460,"wires":[["c5f0738fc74b375b","bbec5b56a85c8470"]]},{"id":"266377c8083bdcc4","type":"function","z":"cb5f1a5e643cfd9e","name":"Delete Data【删除数据】","func":"var id = \"1\";\nmsg.query = \"DELETE FROM device_data where id = '\" + id +\"'\";\n\nreturn msg;","outputs":1,"timeout":"","noerr":0,"initialize":"","finalize":"","libs":[],"x":270,"y":600,"wires":[["c5f0738fc74b375b","bbec5b56a85c8470"]]},{"id":"15d8f7e707d57fe7","type":"inject","z":"cb5f1a5e643cfd9e","name":"Go","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":"","topic":"","payloadType":"date","x":90,"y":600,"wires":[["266377c8083bdcc4"]]},{"id":"f4e615c2ddbfce34","type":"inject","z":"cb5f1a5e643cfd9e","name":"Go","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":"","topic":"","payload":"warehouse","payloadType":"str","x":90,"y":540,"wires":[["6b77fc8e967cc078"]]},{"id":"6b77fc8e967cc078","type":"function","z":"cb5f1a5e643cfd9e","name":"SELECT Data【查询数据】","func":"msg.query = \"SELECT * FROM device_data LIMIT 20\";\n\nreturn msg;","outputs":1,"timeout":"","noerr":0,"initialize":"","finalize":"","libs":[],"x":280,"y":540,"wires":[["c5f0738fc74b375b","bbec5b56a85c8470"]]},{"id":"fe34c177e7e53e67","type":"comment","z":"cb5f1a5e643cfd9e","name":"PostgreSQL常规操作：使用时，注意配置好的数据库地址\\用户名\\密码","info":"","x":270,"y":320,"wires":[]},{"id":"c269a50715b179cf","type":"comment","z":"cb5f1a5e643cfd9e","name":"PostgreSQL模拟测试-双击打开查看【操作表的结构】","info":"-- ----------------------------\n-- Table structure for device_data\n-- ----------------------------\nDROP TABLE IF EXISTS \"public\".\"device_data\";\nCREATE TABLE \"public\".\"device_data\" (\n  \"id\" int4 NOT NULL,\n  \"attribute_name\" varchar(50) COLLATE \"pg_catalog\".\"default\",\n  \"attribute_value\" float8,\n  \"create_time\" timestamp(0)\n)\n;\nCOMMENT ON COLUMN \"public\".\"device_data\".\"id\" IS 'ID';\nCOMMENT ON COLUMN \"public\".\"device_data\".\"attribute_name\" IS '属性名称';\nCOMMENT ON COLUMN \"public\".\"device_data\".\"attribute_value\" IS '属性值';\n\n-- ----------------------------\n-- Primary Key structure for table device_data\n-- ----------------------------\nALTER TABLE \"public\".\"device_data\" ADD CONSTRAINT \"deviceData_pkey\" PRIMARY KEY (\"id\");","x":220,"y":260,"wires":[]},{"id":"b66c05171155f738","type":"comment","z":"cb5f1a5e643cfd9e","name":"双击打开查看【官方使用文档】","info":"官方使用文档：\nhttps://yunteng.yuque.com/to2an3/rrd8yw/oaas8gg93cg4b94q","x":150,"y":200,"wires":[]},{"id":"c5f0738fc74b375b","type":"postgresql","z":"cb5f1a5e643cfd9e","name":"PostgreSQL数据库操作","query":"","postgreSQLConfig":"066ab6a845d1e5d4","split":false,"rowsPerMsg":1,"outputs":1,"x":530,"y":500,"wires":[["b83ef5644643c814"]]},{"id":"b83ef5644643c814","type":"debug","z":"cb5f1a5e643cfd9e","name":"debug 2576","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":730,"y":500,"wires":[]},{"id":"bbec5b56a85c8470","type":"debug","z":"cb5f1a5e643cfd9e","name":"debug 2577","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"query","targetType":"msg","statusVal":"","statusType":"auto","x":510,"y":580,"wires":[]},{"id":"066ab6a845d1e5d4","type":"postgreSQLConfig","name":"DBConnection-local","host":"192.168.1.31","hostFieldType":"str","port":"5432","portFieldType":"num","database":"aiotedge","databaseFieldType":"str","ssl":"false","sslFieldType":"bool","applicationName":"","applicationNameType":"str","max":"10","maxFieldType":"num","idle":"1000","idleFieldType":"num","connectionTimeout":"10000","connectionTimeoutFieldType":"num","user":"postgres","userFieldType":"str","password":"postgres","passwordFieldType":"str"}]