Overview
The postgresql node connects to PostgreSQL databases for reliable, enterprise-grade
data storage. Supports connection pooling, transactions, prepared statements, and all PostgreSQL features
including JSON types, arrays, and full-text search.
ACID
Compliant
Pool
Connections
SSL
Encryption
JSON
Native Support
Properties
| Property | Type | Default | Description |
|---|---|---|---|
| host | string | "localhost" | Database host |
| port | number | 5432 | Database port |
| database | string | "edgeflow" | Database name |
| user | string | "postgres" | Username |
| password | string | "" | Password (use env vars!) |
| ssl | boolean | false | Enable SSL connection |
Output
{
"payload": [
{"id": 1, "name": "Sensor A", "data": {"temp": 23.5}},
{"id": 2, "name": "Sensor B", "data": {"temp": 24.1}}
],
"rowCount": 2,
"command": "SELECT"
}Example: IoT Data with JSONB
Store flexible sensor data using PostgreSQL's JSONB type.
-- Create table with JSONB
CREATE TABLE sensor_data (
id SERIAL PRIMARY KEY,
device_id TEXT NOT NULL,
data JSONB NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- Insert with any JSON structure
INSERT INTO sensor_data (device_id, data)
VALUES ($1, $2);
-- params: ["pi-001", {"temperature": 23.5, "humidity": 45}]
-- Query JSON fields
SELECT device_id, data->>'temperature' as temp
FROM sensor_data
WHERE (data->>'temperature')::numeric > 25;