Node-RED 是一种强大的基于流的开发工具，专注于实现物联网设备、API 以及在线服务之间的无缝连接。它为用户提供了高效便捷的物联网设备接入方式，能够轻松实现数据的实时读取、精准控制与全面监控。在本文中，我们将以 AIoTedge 物联网边缘计算平台集成的 Node-RED 软网关为例，详细展示如何接入 PostgreSQL 数据库。我们精心编写了一份演示 Flow 流程代码，您只需将其导入到您的 Node-RED 环境中，即可轻松实现功能，无需额外的复杂配置，让您能够快速开启数据库接入之旅。

01. 准备工作

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");

02. 详细教程

2-1、示例代码

可导入到你的NodeRED环境中，直接使用。

[{"id":"e1390262b64eaea2","type":"function","z":"cb5f1a5e643cfd9e","name":"INSERT Data【插入数据】","func":"nvar datetime = formatDateTime();//创建日期格式的时间nnvar minBound = 15, maxBound = 25;//创建温度模拟数据15~25nvar randomNum = Math.random() * (maxBound - minBound) + minBound;nvar wendu = randomNum.toFixed(2);nnvar id = "1";nvar attributeName = "温度";nnmsg.query = "INSERT INTO device_data (id,attribute_name,attribute_value,create_time) VALUES "n    + "('" + id + "','" + attributeName + "','" + wendu + "','" + datetime +"')";nnreturn msg;nn//得到当前日期 【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开始，所以需要+1n    var day = now.getDate(); // 获取日期n    var hours = now.getHours(); // 获取小时n    var minutes = now.getMinutes(); // 获取分钟n    var seconds = now.getSeconds(); // 获取秒数nn    // 将单个数字格式化为两位数（例如，1变为01）n    month = month ,"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";nnmsg.query = "UPDATE device_data SET attribute_value = '" + value + "' WHERE id='" + id +"'";nnreturn 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 +"'";nnreturn 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";nnreturn 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_datan-- ----------------------------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 '属性值';nn-- ----------------------------n-- Primary Key structure for table device_datan-- ----------------------------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"}]