PostgreSQL Driver

dc3-driver-postgresql onboards a PostgreSQL database into IoT DC3 as a data source: it runs a SELECT per polling cycle and uses the queried value as the reading, and supports writing values via the UPDATE/INSERT write query configured on a point. By the end you can treat columns of an existing table as polled points, and understand the boundaries of the read and write SQL paths.

Protocol background

Not all data comes from a fieldbus device. A lot of business data, historical data, and results accumulated by third-party systems simply live in a PostgreSQL table—an MES work-order status, the settled cumulative quantity from a metering system, a database view that an upstream platform exposes and nothing else. This kind of data has no fieldbus protocol like Modbus or OPC UA to ride on, yet it is a genuine extension of the device world and needs to be brought into the unified point model to be managed and consumed.

PostgreSQL is an open-source object-relational database (ORDBMS) known for strict SQL-standard compliance, strong transactions (MVCC), and rich data types (JSON/JSONB, arrays, ranges, geometry). It listens on port 5432 by default, and clients connect over standard JDBC (org.postgresql.Driver). Onboarding it into an IoT platform essentially means " treating a database as a class of device": one row, one column of a table becomes the source of a point's value.

In the four-layer IoT reference architecture, a database-bridge driver straddles the boundary between the network and platform layers—it parses no fieldbus frames, but re-admits "data already landed in a table" into the polling pipeline as points. For how this path connects to time-series storage and stream processing, see Time-Series Data & Stream Processing.

How it differs from fieldbus drivers

Drivers like Modbus and OPC UA face "live physical quantities"—registers change in real time, and what you read is the current field value. A database driver faces "data already persisted"—what you read is a snapshot written by some upstream system, possibly already stale. When treating a column as a point, align the polling cadence with the upstream write cadence, or you will repeatedly read the same old value.

Attribute configuration

Onboarding uses three kinds of attributes: driver attributes (device level—which database, which account), point attributes (the read/write SQL per point), and command attributes (a reserved item, currently inert). Every default comes from the driver's application.yml; you fill in concrete values on the Device/Point. For where attributes come from across the three layers, see Attributes & Config.

Driver attributes (device-level driver-attribute)

When onboarding a PostgreSQL database, fill in these attributes on the Device. They decide which database to connect to, which account to use, and the connection timeout:

Attribute	code	Type	Default	Remark
Host	host	STRING	localhost	PostgreSQL host
Port	port	INT	5432	PostgreSQL port
Database	database	STRING	(empty)	Database name to connect to
Username	username	STRING	root	Connection account
Password	password	STRING	(empty)	Account password
Query Timeout	queryTimeout	INT	30	Connection-acquire timeout (seconds); used as the Hikari pool connectionTimeout

The driver builds the JDBC URL from host, port, and database (in the form jdbc:postgresql://host:port/database). Device validation (validate()) checks that all five of host, port, database, username, password are present—any empty one fails; queryTimeout is optional and defaults to 30 seconds. Each device maps to its own HikariCP connection pool inside the driver (maximumPoolSize=5, minimumIdle=1), cached and reused by device ID.

Point attributes (point-attribute)

On each polled Point, fill in its read/write SQL:

Attribute	code	Type	Default	Remark
Read Query	readQuery	STRING	(empty)	SELECT statement for reading the point value
Write Query	writeQuery	STRING	(empty)	UPDATE/INSERT for writing, using a single ? placeholder for the written value (bound as a parameter)

Read Query takes the first column of the first row

readQuery is a plain SELECT; after running it the driver takes the first column of the first row ( rs.getObject(1)) as the point's value, then toString()s it for the point to parse by its data type (Point pointTypeFlag). So a single-row, single-column query like SELECT temperature FROM sensor WHERE id = 1 is the safest form. readQuery is required on a point; point validation ( validatePoint()) fails without it.

Command attributes (command-attribute)

The write command keeps one attribute, but it is not consumed by the implementation:

Attribute	code	Type	Default	Remark
Execute Query	executeQuery	STRING	(empty)	SQL to execute for the command (reserved)

executeQuery is currently not consumed by the implementation

Writing a value actually goes through the point's writeQuery: the driver's write() reads the point-attributewriteQuery and runs the UPDATE/INSERT with prepared-statement parameter binding. The executeQuery on command-attribute is kept only as a configuration item—no code in the current driver reads or executes it; there is no separate "execute a SQL string per command" path. To write values, put the SQL in the point's writeQuery; configuring executeQuery has no effect.

Polling & health

• Polling interval: default cron 0/30 * * * * ? (read once every 30 seconds).
• Custom interval: the driver also has a custom schedule, default cron 0/5 * * * * ? (every 5 seconds); the JDBC database driver's schedule() is an empty implementation, so this schedule currently does nothing.
• Health/online: device health check defaults to cron 0/15 * * * * ? with a lease timeout of 45 seconds. The health check borrows a connection from the pool and uses conn.isValid(5) to decide reachability—if it can't connect, the device goes offline. See Device for the online-state mechanism.

Troubleshooting

When a database-bridge onboarding fails, the cause is rarely the protocol itself—it is the connection parameters, the SQL shape, or the upstream database's state. Ordered by frequency:

Device stays offline / pool won't come up

The health check decides with conn.isValid(5). If a device is always offline, first confirm host/port is reachable (don't use localhost from inside a container to reach the host), database name case is correct, and the username/password account can log into that database. PostgreSQL is also gated by pg_hba.conf—if the source IP or auth method isn't allowed it rejects the connection outright; that error is on the database side, so open it up there.

Read Query must be read-only and resolve to a single value

The driver takes only the first column of the first row of the readQuery result, so the query should return a single row and column and pinpoint the target row with a WHERE primary-key condition. With multiple rows/columns only the first is used and may not be the row you meant; with no result the point value is null. Never put UPDATE/DELETE in readQuery—reading is a read-only path, and writing there corrupts data. Write the filter fully so you don't pick the wrong row as the table's data changes.

Write Query uses a ? placeholder, not ${value}

When writing, writeQuery uses a single ? for the value (e.g. UPDATE sensor SET temperature = ? WHERE id = 1), and the driver binds the command parameter as a JDBC parameter via ps.setString(1, value)—prepared-statement binding, not string concatenation, inherently safe from SQL injection. Do not concatenate the value by hand, and do not use template syntax like ${value}: it would neither be substituted nor give you injection protection. A write is judged successful by "affected rows > 0", so an UPDATE that hits 0 rows (the WHERE matched nothing) counts as a write failure.

Identifier case follows PostgreSQL rules

PostgreSQL folds unquoted identifiers to lowercase and matches double-quoted ones literally. A wrong-case database won't connect; table/column names in readQuery/writeQuery that don't match the case used at creation will report " does not exist". Fill them in with the real case, and double-quote identifiers in the SQL when needed.

Query timeout / slow SQL

queryTimeout (default 30 seconds) is used as the Hikari pool connectionTimeout—the upper bound on waiting to acquire a connection. For large tables or slow SQL, optimize the SQL, add indexes, and narrow the WHERE rather than simply raising the timeout—a slow query holds a pool connection (only 5 exist) and drags down polling of the whole batch.

How it lands in IoT DC3

• Driver name / code: PostgreSQL Driver / PostgresqlDriver (dc3.driver.code is a stable routing identifier the platform routes messages by—don't change it casually).
• Type: DRIVER_CLIENT—the driver actively connects and issues queries; it does not listen for pushes.
• Read / write / subscribe: consistent with the driver capability matrix—read ✓, write ✓, subscribe —. Reads take the first value of a SELECT; writes use the prepared binding of writeQuery; a database has no change subscription, so values are pulled by periodic polling.

Implementation status: available (not a skeleton)

The PostgreSQL driver is an available implementation, not a skeleton. Connection, read, write, and health check are all provided by the shared dc3-common-sql abstract base service AbstractJdbcDriverCustomService (the same logic shared with MySQL, Oracle, and SQL Server); the PostgreSQL subclass only supplies JDBC URL construction, the driver class name org.postgresql.Driver, and the default port 5432. The one caveat is the command-attributeexecuteQuery, which is reserved but unwired (see Attribute configuration above).

Minimal onboarding example

Onboard the temperature column of the id=1 row in a sensor table as a temperature point:

1. Create a Device with PostgreSQL Driver, and set the driver attributes host=192.168.1.10, port=5432, database=iot, username=root, password=******.
2. Add a temperature Point (pointTypeFlag=FLOAT, READ_ONLY) to the Profile bound to the device, and set the point attribute readQuery=SELECT temperature FROM sensor WHERE id = 1.
3. Start the driver, and within 30 seconds the queried temperature shows up in the PointValue.

For a writable point, also set writeQuery=UPDATE sensor SET temperature = ? WHERE id = 1 on the same point and make its rwFlag writable. For a complete walkthrough, see Device Onboarding.

Further reading

• Drivers Overview — categorization and selection map of all drivers
• Driver Capability Matrix — read/write/subscribe capabilities at a glance
• Device Onboarding — a complete onboarding walkthrough
• Time-Series Data & Stream Processing — how polled point values land in the time-series store and feed stream processing
• MySQL Driver — another JDBC database data source with an identical configuration structure