Skip to content
CelestianCelestian Documentation

Rule to PostgreSQL: many rows

The PostgreSQL output normally inserts one row per message. A SenFlat v2 message from a gateway can carry many sources (s[]), and a history message (h=1) carries a series of time points. To insert all of them, turn on the One row per result item switch on the PostgreSQL node, and flatten the message into a list of rows.

This guide uses the wide shape: one row per (source, time), with the columns being the metrics. History messages are included: points with the same time are merged into one row.

The simplest way: the SenFlat v2 splitter node

Section titled “The simplest way: the SenFlat v2 splitter node”

The built-in SenFlat v2 splitter → rows logic flattens the message without code. Every metric present at one time becomes a row key. A message that is not SenFlat v2 is not forwarded.

The base columns of each row: device_id, t_ms (epoch milliseconds), time (RFC 3339 UTC), time_trusted, history (a history message), and q.

An example of one rule for several needs at once:

Channel Subscriber → SenFlat v2 splitter
   ├─ Bypass ────────────────────────────────────────→ PostgreSQL (all rows, including history; atomic)
   ├─ Comparison (temp_inlet > 30, Live data only) → Telegram           (the notification trigger)
   ├─ Comparison (temp_inlet > 30, Live + fresh history) → Telegram     (the last 5 minutes of history count)
   ├─ Bypass → Device alarm                                              (follows its own history window)
   └─ Comparison (temp_inlet > 30, All data) → Webhook                  (all data that meets the condition)

The behavior of nodes below the splitter:

  • Comparison evaluates each row and forwards only those that qualify. The path uses the row key: message.payload.temp_inlet.
  • PostgreSQL receives the whole list in one atomic INSERT.
  • Alarm, Device alarm, E-Mail, Telegram, Webhook, and Channel Publisher run once per row, in time order, with that row as .Result. At most 500 rows per message per output; more than that and the node is marked as an error, so filter with a Comparison first.
  • Internal DB does not accept a list of rows. To still store into Internal DB, build a separate branch: Channel Subscriber → Bypass → Internal DB.

An example table

Section titled “An example table”
CREATE TABLE telemetry (
  device_id        text             NOT NULL,
  ts               timestamptz      NOT NULL,
  ts_trusted       boolean          NOT NULL,
  is_history       boolean          NOT NULL,
  q                bigint,
  temp_inlet       double precision,
  temp_outlet      double precision,
  voltage          double precision,
  current          double precision,
  power_draw       double precision,
  energy           double precision,
  door_front_open  boolean
);
CREATE INDEX ON telemetry (device_id, ts);

Column mapping

Section titled “Column mapping”
Column Value template
device_id {{.Row.device_id}}
ts {{.Row.time}}
ts_trusted {{.Row.time_trusted}}
is_history {{.Row.history}}
q {{printf "%.0f" .Row.q}}
A metric, e.g. temp_inlet {{.Row.temp_inlet}}

Template notes:

  • Values are sent as text parameters and converted by PostgreSQL to the column type. A metric that is not on that row becomes NULL.
  • A large number such as q is printed in exponent notation without a format: use {{printf "%.0f" .Row.q}}.
  • Do not use printf "%.1f" on a metric that can be missing: an empty value prints %!f(<nil>).
  • The tsms function turns epoch millisecond time into RFC 3339 UTC text: {{tsms .Row.t_ms}}.

An alternative with Lua

Section titled “An alternative with Lua”

When you need to change values, limit metrics, or shape your own columns, use Lua logic that returns an array of objects, then turn on One row per result item. A ready-made script flattens SenFlat v2 (including history, with trusted-time marking):

Download senflat2-lebar.lua

The rules for the logic result in this mode:

Logic result Behavior
An array of objects One row per element.
A single object One row.
nil, {}, or an empty array Writes nothing (not an error).
Any other value (a number, text) An error “the logic result must be an array of objects”.
More than 500 elements An error, not silently truncated.

Remember: a rule script must have the form function logicFunction() … end followed by return logicFunction().

Steps in the editor

Section titled “Steps in the editor”
  1. Build a rule: Channel Subscriber (the gateway channel) → SenFlat v2 splitter (or Lua) → PostgreSQL.
  2. On the PostgreSQL node fill in the connection, table, and columns as above, then turn on One row per result item.
  3. Use Test Rule with a sample payload. The preview shows the rowCount and the first 20 rows without writing anything. Test connection checks the table, columns, and the INSERT privilege.
  4. Enable the rule.