PostgreSQL uses driver version postgresql42.2.26 and currently supports PostgreSQL 9.6 and above.
Use Limits
Note:
Supports reading view tables.
1. PostgreSQL data sources support the password authentication method (support SCRAM-SHA-256). If the password and authentication method are changed on the PostgreSQL database side, you need to update the data source configuration, re-test connectivity, and manually run related task verification.
2. When Table Name or Field name in PostgreSQL starts with Numbers or contains Upper and Lowercase English Letters or a Dash (-), you need to use Double quotes ("") to Escape. Failing to do so may cause the PostgreSQL Plugin to Read or write data Failed. However, in the PostgreSQL Reader and Writer plugin, Double quotes ("") are JSON Keywords, so you need to use a Backslash (\\) to Escape Double quotes ("") again. For example, a Table Name "123Test" should be escaped as \\"123Test\\".
Note:
In Double quotes (""), both the leading (") and trailing (") quotes need to be Escaped using a Backslash (\\).
Wizard Mode does not support Escape; you need to switch to Script Mode to perform Escape.
The Sample Code for using Script Mode for Escape is as follows:
"parameter":{
"datasource":"abc",
"column":[
"id",
"\\"123Test\\"",//Adding escape characters
],
"where":"",
"splitPk":"id",
"table":"public.wpw_test"
}
PostgreSQL offline single table read node configuration
Parameters
Description
Data Source
Available PostgreSQL data source.
Database
Supports selecting or manually entering the database name to be read
By default, the database bound to the data source is used as the default database. Other databases need to be manually entered.
If the data source network is not connected and the database information cannot be fetched directly, you can manually enter the database name. Data synchronization can still be performed when the DataInLong network is connected.
Schema
Supports selection or manual input of the Schema name to be read.
Table
Supports selecting or manually entering the table name to be read
In the case of partitioned tables, you can select or enter multiple table names on the PostgreSQL source side. Multiple tables must have consistent structures.
In the case of table partitioning, the table index range can be configured. For instance, 'table_[0-99]' indicates reading 'table_0', 'table_1', 'table_2' up to 'table_99'; if the numeric suffix of your tables has a consistent length, like 'table_000','table_001','table_002' up to 'table_999', you can configure it as '"table": ["table_00[0-9]", "table_0[10-99]", "table_[100-999]"]'.
If the data source network is not connected and the table information cannot be fetched directly, you can manually enter the table name. Data synchronization can still be performed when the DataInLong network is connected.
Add Sharded Database/Table
Suitable for multi-database scenarios, click to configure multiple data sources, libraries, and table information.
Note:
When selecting multiple table objects in a multi-database, multi-table scenario, the Schema information (including field names and field types) must be consistent. The system defaults to displaying the metadata field information of the first table of the first data source within the data fields module. If fields differ among tables, it may cause runtime failures.
Split Key
You can use a certain column in the source data table as the partition key; it is recommended to use a primary key or an indexed column as the partition key. Only fields of type integer are supported. During data reading, data sharding is performed based on the configured field, enabling concurrent reading and improving data synchronization efficiency.
Filter Conditions (Optional)
PostgreSQL concatenates the SQL based on the specified WHERE condition and performs data extraction according to that SQL. For example, during testing, you can specify the WHERE condition for the actual business scenario, usually syncing the data of the day by setting the WHERE condition to id>2 and sex=1.
PostgreSQL offline single table write node configuration
Parameters
Description
Data Destination
PostgreSQL data source that needs to be written.
Database
Supports selection or manual input of the database name to write to
By default, the database bound to the data source is used as the default database. Other databases need to be manually entered.
If the data source network is not connected and the database information cannot be fetched directly, you can manually enter the database name. Data synchronization can still be performed when the DataInLong network is connected.
Schema
Supports selection or manual input of the Schema name to be read.
Table
Supports selection or manual input of the table name to write to
If the data source network is not connected and the table information cannot be fetched directly, you can manually enter the table name. Data synchronization can still be performed when the DataInLong network is connected.
Whether to Clear Table
You can manually choose whether to clear the PostgreSQL data table before writing to it.
Write Mode
PostgreSQL writing supports two modes:
append: Append write.
upsert: Update and write data based on the set primary key fields.
Batch Submission Size
The record size of one-time batch submission can significantly reduce the number of network interactions between the data synchronization system and PostgreSQL, and improve overall throughput. If this value is set too large, data synchronization process may encounter OOM exceptions.
Pre-Executed SQL(Optional)
The SQL statement executed before the synchronization task. Fill in the correct SQL syntax according to the data source type, such as clearing the old data in the table before execution (truncate table tablename).
Post-Executed SQL (Optional)
SQL statements to be executed after performing the synchronization task. Fill in the correct SQL syntax according to the data source type, for example, adding a timestamp: alter table tablename add colname timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.
Data type conversion support
Read
The supported data types and corresponding conversion relationships for PostgreSQL reading are as follows (when processing PostgreSQL, the data types of the PostgreSQL data source will be mapped to the data types of the data processing engine first):
