Postgre SQL
PostgreSQL is a relational database platform used for transactional systems, reporting workloads, and application backends.
The Flowgear Postgre SQL Node runs SQL queries and upserts JSON rows into a target table from a Workflow.
Revision History
0.0.0.3 - Initial release.
0.0.0.5 - Optimized template discovery.
0.0.0.6 - Updated the Node icon.
Connection
Use the Connection to configure the PostgreSQL server, credentials, and SSL settings.
| Property | Type | Description |
|---|---|---|
Server |
String | The name or IP address of the server hosting the PostgreSQL instance. |
Port |
Integer | The PostgreSQL server port. The default is 5432. |
Database |
String | The name of the database to connect to. |
Username |
String | The PostgreSQL username used for the Connection. |
Password |
Masked | The PostgreSQL password used for the Connection. |
SslMode |
SslMode | Determines the SSL configuration. Options include Disable, Allow, Prefer, Require, VerifyCA, and VerifyFull. |
Trust Server Certificate |
Boolean | When true, the Connection trusts the server certificate without validating it. |
Connection Timeout |
Integer | The time in seconds to wait while establishing a Connection. The minimum and default value is 15. |
Enable Detailed Responses |
Boolean | When true, error responses include more detail. |
Enable Legacy Timestamp Behavior |
Boolean | When true, the Node enables the timestamp behavior used before Npgsql 6.0 and disables DateTime infinity conversions. |
Setup Notes
Enter Server, Database, and Username, then configure Password and the SSL Properties required by your PostgreSQL server. Test the Connection after saving it.
Enable Trust Server Certificate only when your environment requires it. Use VerifyCA or VerifyFull for SslMode when the server certificate must be validated.
Methods
The Postgre SQL Node exposes methods for running SQL queries and upserting rows into a table.
Query
Executes a query against a PostgreSQL database and returns the results.
| Parameter | Type | Description |
|---|---|---|
Connection |
Connection | The Postgre SQL Connection. |
Query |
String | The SQL query to execute. |
| Return | Type | Description |
|---|---|---|
Items |
Array | The business rows returned by the query. Each row is emitted as an object. |
Upsert Table
Upserts data into a PostgreSQL database table.
| Parameter | Type | Description |
|---|---|---|
Connection |
Connection | The Postgre SQL Connection. |
Table Name |
String | The name of the table to upsert. |
Key Fields |
String | A comma-delimited list of key fields used to resolve conflicts during the upsert operation. |
Items |
Array | The items to upsert. |
| Return | Type | Description |
|---|---|---|
RowsAffected |
Integer | The number of rows affected by the upsert operation. |
Usage Notes
Queryemits one output item per row in the result set.- For
Upsert Table, use item Property names that match the destination column names. - Separate multiple
Key Fieldswith commas. - Make sure each input item contains the fields required by the destination table and the fields named in
Key Fields. - Enable
Enable Legacy Timestamp Behavioronly when an existing Workflow depends on the Npgsql behavior used before version 6.0.