PostgreSQL
Collection POSTGRES and Delivery POSTGRES_EXEC. The same connection serves TimescaleDB,
which is a PostgreSQL extension — there is no separate type for it.
Connection
If the TimescaleDB extension is installed on the database, Test Connection reports its version
alongside the server version (PostgreSQL 16.2 (TimescaleDB 2.17.0)).
| Field | What it does |
|---|---|
| Host / Port | PostgreSQL server. Defaults to 5432 |
| Database | Database opened on the connection |
| Schema (search_path) | Default schema for queries. Blank = public. Accepts a single plain name; to reach another schema, qualify the table in the query itself |
| User / Password | Database user |
| SSL Mode | Disabled (no TLS), Require TLS (encrypts without validating the certificate), Verify CA (validates the chain) or Verify CA and hostname |
| CA Certificate (PEM) | Only for the verifying modes: paste the certificate of the CA that signed the server certificate. Blank when the CA is public (e.g. RDS) |
| Application Name | Identifies the CMS session in the customer’s pg_stat_activity. Blank = CMS |
| Test Query (Keep Alive) | Same purpose as the others. Blank = SELECT 1 |
Verify CA validates the chain but not the server name — the right mode when the database is
reached by IP and the certificate was issued for the DNS name, a common situation on an industrial
network. The libpq allow/prefer modes were deliberately left out: they silently fall back to
cleartext, and here it is better for the connection to fail than to downgrade unnoticed.
The :: cast lives alongside the placeholders
The PostgreSQL driver only takes positional parameters ($1, $2), so CMS converts the :name
placeholders before executing. The conversion understands the dialect: a ::type cast is not
mistaken for a placeholder, and :name inside a string, a quoted identifier, a comment or a
$$...$$ block stays text rather than becoming a bind.
INSERT INTO measurements (ts, data, plant)
VALUES (:datetime::timestamptz, :payload::jsonb, :DEFAULT_PLANT)If a :name matches no Input Parameter, Variable or message value, the execution fails naming the
placeholder that has no value — instead of letting the database answer with a syntax error pointing
somewhere else in the command.
TimescaleDB
There is nothing extra to configure: writing to a hypertable is a plain INSERT, and the time
bucketing functions work in the SELECT Query like any other function.
-- Collection: 5-minute averages over the last hour
SELECT time_bucket('5 minutes', ts) AS bucket, avg(value) AS average
FROM measurements
WHERE ts > now() - interval '1 hour'
GROUP BY bucket ORDER BY bucket;
-- Delivery into a hypertable, ignoring a duplicate timestamp
INSERT INTO measurements (ts, value) VALUES (:datetime, :payload::numeric)
ON CONFLICT (ts) DO NOTHING;Delivery runs one command per message. For very high ingestion volume, the way today is to
group the samples into a single payload (via a Transformer) and write them with a multi-row
INSERT, rather than one message per sample. Native bulk loading (COPY) is not available —
COPY is blocked by the guard.
Vocabulary blocked in this dialect
On top of the DDL/DCL barred in every dialect, this one blocks:
COPY, dblink, pg_read_file, pg_write_file, pg_ls_dir, lo_import, lo_export, pg_sleep, pg_terminate_backend
The full check — leading verb, chained statements and the neutralisation of comments and literals — is described in what CMS blocks in SQL fields.
The behaviour shared by all four databases — result modes, marking what has been read and placeholders — is described in Databases.