This topic describes the data types and parameters that are supported by PostgreSQL Writer and how to configure PostgreSQL Writer by using the codeless user interface (UI) and code editor.

PostgreSQL Writer writes data to tables stored in an PostgreSQL database. PostgreSQL Writer connects to a remote PostgreSQL database by using Java Database Connectivity (JDBC), and executes an SQL statement to write data to the PostgreSQL database.
Note Before you configure PostgreSQL Writer, you must configure a PostgreSQL data source. For more information, see Configure a PostgreSQL connection.
  • PostgreSQL Writer generates the SQL statement based on the table, column, and where parameters that you specified, and sends the generated SQL statement to the PostgreSQL database.
  • If you specify the querySql parameter, PostgreSQL Writer directly sends the value of this parameter to the PostgreSQL database.

Precautions

If the name of a PostgreSQL table or a field starts with a digit or the name contains a letter or a hyphen (-), use double quotation marks (") to escape the name. If you do not escape the name, an error occurs when you use PostgreSQL Writer to write data to the PostgreSQL database. For PostgreSQL Writer, double quotation marks (") are keywords in JSON code. Therefore, you must use backslashes (\) to escape the double quotation marks (") that you use. For example, if the name of a PostgreSQL table is 123Test, the name becomes \"123Test\" after it is escaped.
Note
  • Each double quotation mark (") must be escaped by using a backslash (\).
  • You can use only the code editor to perform escaping.
The following code provides an example on how to use the code editor to perform escaping: 
"parameter": {
    "datasource": "abc",
    "column": [
        "id",
        "\"123Test\"", // Add escape characters.
],
"where": "",
"splitPk": "id",
"table": "public.wpw_test"
},

Data types

PostgreSQL Writer supports most PostgreSQL data types. Make sure that the data types of your database are supported.

The following table lists the data types supported by PostgreSQL Writer.
Data Integration data type PostgreSQL data type
LONG BIGINT, BIGSERIAL, INTEGER, SMALLINT, and SERIAL
DOUBLE DOUBLE, PRECISION, MONEY, NUMERIC, and REAL
STRING VARCHAR, CHAR, TEXT, BIT, and INET
DATE DATE, TIME, and TIMESTAMP
BOOLEAN BOOL
BYTES BYTEA
Note
  • PostgreSQL Writer supports only the data types that are listed in the preceding table.
  • You can convert the MONEY, INET, and BIT data types by using syntax such as a_inet::varchar.

Parameters

Parameter Description Required Default value
datasource The name of the data source. It must be the same as the name of the added data source. You can add data sources by using the code editor. Yes No default value
table The name of the table to which you want to write data. Yes No default value
writeMode The write mode. Valid values:
  • insert: executes the insert into...values... statement to write data to the PostgreSQL database. If a primary key conflict or unique index conflict occurs, data cannot be written to the conflicting rows, and the data that is not written to these rows is regarded as dirty data. We recommend that you use the insert mode.
  • copy: copies data between tables and the standard input or output file. Data Integration supports the COPY FROM command, which allows you to copy data from files to tables. We recommend that you use this mode if performance issues occur.
No insert
column The names of the columns to which you want to write data. Separate the names with commas (,), "column":["id","name","age"]. If you want to write data to all the columns in the destination table, set this parameter to an asterisk (*), such as "column":["*"]. Yes No default value
preSql The SQL statement that you want to execute before the synchronization node is run. For example, you can set this parameter to the SQL statement that is used to delete outdated data. You can execute only one SQL statement on the codeless UI and multiple SQL statements in the code editor. No No default value
postSql The SQL statement that you want to execute after the synchronization node is run. For example, you can set this parameter to the SQL statement that is used to add a timestamp. You can execute only one SQL statement on the codeless UI and multiple SQL statements in the code editor. No No default value
batchSize The number of data records to write at a time. Set this parameter to an appropriate value based on your business requirements. This greatly reduces the interactions between Data Integration and PostgreSQL and increases throughput. If you set this parameter to an excessively large value, an out of memory (OOM) error may occur during data synchronization. No 1,024
pgType The PostgreSQL configuration for converting data types. Valid values: bigint[], double[], text[], jsonb, and json. Example: 
{
    "job":
    {
        "content":
        [{
            "reader": {...},
            "writer":
            {
                "parameter":
                {
                    "column":
                    [
                        // The names of the columns to which you want to write data.
                        "bigint_arr",
                        "double_arr",
                        "text_arr",
                        "jsonb_obj",
                        "json_obj"
                    ],
                    "pgType":
                    {
                        // The configuration that is used to convert data types. In each key-value pair, the key specifies the name of a field in the destination table, and the value specifies the data type of the field. 
                        "bigint_arr": "bigint[]",
                        "double_arr": "double[]",
                        "text_arr": "text[]",
                        "jsonb_obj": "jsonb",
                        "json_obj": "json"
                    }

                }
            }
        }]
    }
}
 No No default value

Configure PostgreSQL Writer by using the codeless UI

  1. Configure data sources.
    Configure Source and Target for the synchronization node. Connections
    Parameter Description
    Connection The name of the data source to which you want to write data. This parameter is equivalent to the datasource parameter that is described in the preceding section.
    Table The table to which you want to write data. This parameter is equivalent to the table parameter that is described in the preceding section.
    Statement Run Before Writing The SQL statement that you want to execute before the synchronization node is run. This parameter is equivalent to the preSql parameter that is described in the preceding section.
    Statement Run After Writing The SQL statement that you want to execute after the synchronization node is run. This parameter is equivalent to the postSql parameter that is described in the preceding section.
    Write Method The write mode. This parameter is equivalent to the writeMode parameter that is described in the preceding section. Valid values: insert and copy.
  2. Configure field mappings. This operation is equivalent to setting the column parameter that is described in the preceding section. Fields in the source on the left have a one-to-one mapping with fields in the destination on the right. Field mappings
    Operation Description
    Map Fields with the Same Name Click Map Fields with the Same Name to establish mappings between fields with the same name. The data types of the fields must match.
    Map Fields in the Same Line Click Map Fields in the Same Line to establish mappings between fields in the same row. The data types of the fields must match.
    Delete All Mappings Click Delete All Mappings to remove the mappings that are established.
    Auto Layout Click Auto Layout. Then, the system automatically sorts the fields based on specific rules.

Configure PostgreSQL Writer by using the code editor

You can configure PostgreSQL Writer by using the code editor. For more information, see Create a sync node by using the code editor.

In the following code, a synchronization node is configured to write data to a PostgreSQL database. For more information about the parameters, see the preceding parameter description. 
{
    "type":"job",
    "version":"2.0",// The version number. 
    "steps":[ 
        {
            "stepType":"stream",
            "parameter":{},
            "name":"Reader",
            "category":"reader"
        },
        {
            "stepType":"postgresql",// The writer type. 
            "parameter":{
                "postSql":[],// The SQL statement that you want to execute after the synchronization node is run. 
                "datasource":"// The name of the data source. 
                    "col1",
                    "col2"
                ],
                "table":"",// The name of the table to which you want to write data. 
                "preSql":[]// The SQL statement that you want to execute before the synchronization node is run. 
            },
            "name":"Writer",
            "category":"writer"
        }
    ],
    "setting":{
        "errorLimit":{
            "record":"0"// The maximum number of dirty data records allowed.
        },
        "speed":{
            "throttle":true,// Specifies whether to enable bandwidth throttling. The value false indicates that bandwidth throttling is disabled, and the value true indicates that bandwidth throttling is enabled. The mbps parameter takes effect only when the throttle parameter is set to true. 
            "concurrent":1, // The maximum number of parallel threads. 
            "mbps":"12"// The maximum transmission rate.
        }
    },
    "order":{
        "hops":[
            {
                "from":"Reader",
                "to":"Writer"
            }
        ]
    }
}