Devart PostgreSql Destination is designed for loading data into PostgreSQL tables.
For data migration to the PostgreSQL database, Devart PostgreSql Destination component uses the COPY command.
The PostgreSQL table you want to migrate data to can be selected in the Table Name combo box on the Component Properties tab of the Devart PostgreSql Destination data flow object.
The action that should be performed with data during migration is defined by the Action parameter of Devart PostgreSql Destination object, that provides the following options: Insert, Update, Delete, and BulkInsert. As all of the actions have certain peculiarities:
- Insert - as this case is the most common, we decided to cover it in a detailed tutorial. It uses simple INSERT statements to load data to PostgreSQL.
- BulkInsert - uses PostgreSQL-specific technique to quickly load data to PostgreSQL. For data migration to the PostgreSQL database, Devart PostgreSql Destination component uses the COPY command.
- Update - Devart PostgreSql Destination component in the Update mode enables updating
data in a PostgreSQL table.
Note:Peculiarity of this action consists in the fact, that performing it requires using primary key values of the PostgreSQL table. Thus, when performing an update, you must know and specify the primary key values of the PostgreSQL table to update a record.
- Delete - Devart PostgreSql Destination component enables deleting data from a
PostgreSQL table.
Note:
As is the case with the Update action, the ID column from the local data source corresponding to the Primary key values column in the PostgreSQL table, must be used.
The amount of data that can be migrated at a time is limited by the BufferSize property.
BufferSize defines the maximum size of the local buffer, its default value is 262144.
Additionally, Devart PostgreSql Destination allows setting the BulkInsertMode property, which determines the mode to use for data loading. It can be text or binary.
Devart PostgreSQL Data Flow components support error outputs, which allow you to manage how the component treats row-level errors in both incoming and outgoing data.
The error output includes the column set taking part in the data flow and additional columns, they are ErrorCode that identifies the error, ErrorColumn that contains the lineage identifier of the error column and ErrorDescription that provides a detailed description of the error.
The ErrorCode column can accept the following values:
| ErrorCode | Description |
|---|---|
| -1 | Means that the record is valid. |
| >0 | Means that the record causes an error. |
Additionally, when the primary key values are automatically generated for the target table, Devart PostgreSql Destination retrieves the generated values and adds them to its output columns.