The PostgreSQL Connector connects SIA Connect to a PostgreSQL database. Use read items to run queries and return database values. Use mappings and the automatically created write item to send complete SQL statements to the database.

This guide explains how to create a PostgreSQL instance, configure read and write queries, interpret returned values, verify the connection, and troubleshoot common errors.

Before you begin

Make sure that you have:

• Installed the PostgreSQL Connector.
• The hostname or IP address of the PostgreSQL server.
• The server port. The PostgreSQL default is 5432.
• The database name.
• A PostgreSQL username and password.
• Configured PostgreSQL to accept a connection from SIA Connect.
• Allowed the connection through any network firewalls.
• Granted the database role permission to run the required queries.

Create a PostgreSQL instance

1. In the Edge Portal, go to Instances.
2. Select Add instance.
3. Select the PostgreSQL Connector.
4. Enter a name for the instance.
5. Enter the PostgreSQL server hostname or IP address in Address.
6. Complete the Connector parameters.
7. Select Create.

The instance attempts to connect when it is enabled and used.

Instance parameters

Create a read item

A read item runs the statement in Query default whenever the item is read. The result is stored as the item value.

1. Open the PostgreSQL instance.
2. Open the Items tab.
3. Select Add item.
4. Enter a name and UID for the item.
5. Set the item to allow reading.
6. Enter a read query in Query default.
7. Configure the required read interval or event-based behavior.
8. Select Create.

For example:

SELECT temperature, status
FROM machine_state
WHERE machine_id = 1
ORDER BY recorded_at DESC
LIMIT 1

The returned item value is a compact JSON array. A result could look like this:

[{"temperature":21.5,"status":"running"}]

Avoid unrestricted queries against large tables. Use filters, LIMIT, and an appropriate ORDER BY clause to control the returned data.

Read data from a PostgreSQL function

If a PostgreSQL function returns rows, call it from a SELECT statement in Query default.

For example:

SELECT *
FROM get_latest_machine_state(1)

The function result is returned using the same JSON array format as a table query. Confirm that the configured PostgreSQL role has permission to execute the function and read any data used by it.

Write data to PostgreSQL

Each enabled PostgreSQL instance automatically receives a write-only item named Query write. Its UID ends with _WRITE.

Map a complete SQL statement to the write item. The Connector executes the incoming item value directly as SQL.

For example, the incoming value could be:

INSERT INTO measurements (source, value)
VALUES ('line_1_temperature', 21.5)

You can also send an UPDATE, DELETE, or function or procedure call if the database role is authorized to execute it.

Understand returned values

Query results are returned as a JSON array. Each database row becomes an object, and each selected column becomes a property.

Verify the configuration

1. Confirm that the instance and required items are enabled.
2. Open the instance and check its status and most recent error.
3. Open a read item and confirm that its value contains the expected JSON result.
4. Trigger a controlled test mapping to Query write.
5. Confirm the inserted or updated value in PostgreSQL.
6. Review the item, mapping, and system logs if the expected result is not returned.

Troubleshoot the PostgreSQL Connector

The instance reports Failed to open database

• Confirm the Address, Port, Username, Password, and Database values.
• Confirm that PostgreSQL is running and listening on the configured address and port.
• Check routing, DNS resolution, and firewall rules between SIA Connect and PostgreSQL.
• Confirm that PostgreSQL accepts connections from the SIA Connect network.

The connection is refused

Confirm that PostgreSQL is listening on the configured TCP interface and port. Check the server's listen_addresses setting and the host firewall.

PostgreSQL reports password authentication failed

Confirm the username and password. Also confirm that the selected authentication method supports that role and connection.

PostgreSQL reports no pg_hba.conf entry

Add an appropriate rule for the SIA Connect address, database, role, and required authentication method in pg_hba.conf. Reload the PostgreSQL configuration after making the change.

The database does not exist

Confirm the Database value. PostgreSQL database names can be case-sensitive when quoted, so use the exact intended name.

The query reports relation does not exist

Confirm the table or view name and the active schema search path. Use a schema-qualified name such as public.machine_state when needed.

The query reports a syntax error

Run the same statement in a PostgreSQL query tool using the same database role. Correct PostgreSQL-specific syntax, quoting, casts, or function parameters before updating Query default or the mapping.

The query reports permission denied

Grant only the required permissions on the database, schema, table, sequence, function, or procedure. Remember that an INSERT can also require permission to use a sequence.

A read query is blocked

Move modifying statements to a mapping that writes to Query write. Read items reject detected UPDATE, DELETE, INSERT INTO, DROP, and TRUNCATE statements.

The Connector reports Empty query

Confirm that Query default is not empty and that the statement returns a result set. For a database function, confirm that it returns rows rather than only performing an action.

Large integers or NUMERIC values are returned as strings

This protects values from precision loss in JSON. Keep the value as a string, or explicitly convert it in PostgreSQL when a numeric JSON value is required and the reduced precision is acceptable.

JSON or JSONB has a different structure than expected

PostgreSQL JSON and JSONB values are inserted into the returned result as nested JSON. Update the mapping to read the required property or array element from that nested value.

A write mapping fails

Review the mapping error. It includes the database error and the SQL statement that was executed. Confirm that the incoming value is a complete PostgreSQL statement. Check quoting, type conversions, length constraints, unique keys, foreign keys, deadlocks, and permissions.

The connection drops after previously working

The Connector marks recognized connection failures for reconnection on a later access. If the error continues, check PostgreSQL availability, connection limits, server logs, and the network path.

Related articles

• Install and manage Connectors
• Create and edit instances
• Create and edit items
• Create and edit mappings
• Troubleshoot Connector, instance, item, and mapping errors
• Configure and review system logs