PostgreSQL Query
Executes PostgreSQL queries against a PostgreSQL database and returns the result set as an XML document.
Revision History
1.0.0.0 Initial Release
1.0.0.2 Minor Update
1.0.0.3 Added Port in Connection
1.0.0.5 Update to support in the 23 refresh
1.0.0.10 Add support for legacy parsing and detailed responses
1.0.0.11 Minor Update
Properties
Connection
Type: Connection
Server
Type: String
The name or IP address of the server hosting the PostgreSQL Server instance. Specify a DropPoint if you need to access a PostgreSQL instance that is not Internet-exposed.
Port
Type: String
The port to connect to.
Database
Type: String
The name of the database to connect to.
Username
Type: String
The PostgreSQL username to be used for the connection.
Password
Type: Password
The PostgreSQL password to be used for the connection.
SSLMode
Type: List
Determines what SSL configuration to use. See Client Verification of Server Certificates.
TrustServerCertificate
Type: Boolean
Whether to trust the server certificate without validating it.
ConnectionTimeout
Type: Int32
The time to wait (in seconds) while trying to establish a connection before terminating the attempt and generating an error. Default is 15 seconds.
To set the Command Timeout, use the Timeout property in the Advanced properties of the Connection. Default is 30 seconds.
EnableDetailedResponses
Type: Boolean
Includes more verbose error responses. Warning, this could contain more sensitive information. When enabled the Include Error Detail Connection String parameter is included.
EnableLegacyTimestampBehavior
Type: Boolean
Revert to the legacy timestamp behavior prior to Npgsql version 6.0, this setting enables Npgsql.EnableLegacyTimestampBehavior and Npgsql.DisableDateTimeInfinityConversions.
This setting needs to be set on ALL Connections of PostgreSQL and PostgreSQL Table Update Nodes regardless of Connecting to the same server. If it is run on a DropPoint, the DropPoint Service needs to be restarted for this to take effect. For more info on this breaking change and how to migrate the timestamp columns to timestamptz.
Npgsql 6.0 Release Notes
Remarks
Use this Connector to execute PostgreSQL queries against a PostgreSQL Server instance.
PostgreSQL Parameters via Custom Properties
Flowgear Custom Properties can be added to this Node to make parameters available in the Query Property. Use of a Custom Property is shown in the linked example workflow (see Examples below).
Other Nodes for PostgreSQL databases
If you are updating values in a table based on keys, consider using PostgreSQL Table Update instead of crafting update statements.
Examples
The patterns from Sample Workflow and Sample Workflow can be used to request and emit a single page of data respectively. Simply replace the Microsoft SQL Query Node with the PostgreSQL node found here: Sample Workflow.