PostgreSQL Table Update

Provides an efficient way for executing bulk insert/update on a database without having to hand-write INSERT or UPDATE statements.

Revision History

1.0.0.1 Initial Release
1.0.0.3 Added Port in Connection
1.0.0.5 Updated for the 23 Refresh
1.0.0.10 Added 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

TableXml

Type: Xml
An XML representation of the data to be updated or inserted (see Remarks).

KeyFields

Type: String
A comma-separated list of fields that represent key fields for the update or insert operation. These values will be matched in the target table to determine whether the row already exists. The key fields listed in this property must be supplied in the TableXml property. It is not necessary to supply key fields if AllowUpdate is false.

AllowInsert

Type: Boolean
Indicates whether inserts are allowed into the target table.

AllowUpdate

Type: Boolean
Indicates whether updates are allowed into the target table (requires at least one field to be specified in KeyFields).

RowsAffected

Type: Int32
Returns the number of rows affected by the insert/update operation.

Remarks

The XML supplied should contain the following paths root\tablename\columnname. The name of the root node is not important. The second tier should contain the table name and only a single table can be updated per call. The third tier should contain the column name.

Here's an example of a payload that will add or insert two rows into a table called inventory :

<root>
	<inventory>
		<code>123</code>
		<description>first inventory item</description>
	</inventory>
	<inventory>
		<code>456</code>
		<description>second inventory item</description>
	</inventory>
</root>

When AllowUpdate is true, the fields specified in KeyFields will be matched from TableXml to the target table. All KeyFields supplied must exist in both TableXml and the target table. Multiple key fields should be separated with commas. Note that the specified KeyFields do not have to be the database primary key fields but should indicate the key fields for purposes of the update operation.

For example, if an inventory table contains the fields id (int), code (varchar(50)), description (varchar(100)), the KeyFields property could be set to code if it was necessary to update based on this value rather than the internal integer id field.