Postgresql Writer

Postgresql Writer plugin implements the functionality of writing data to PostgreSQL database tables.

Example

The following configuration demonstrates reading data from a specified PostgreSQL table and inserting it into another table with the same table structure, to test the data types supported by this plugin.

Table Structure Information

Assume the table creation statement and input insertion statement are as follows:

create table if not exists addax_tbl
(
    c_bigint bigint,
    c_bit bit(3),
    c_bool boolean,
    c_byte bytea,
    c_char char(10),
    c_varchar varchar(20),
    c_date date,
    c_double float8,
    c_int integer,
    c_json json,
    c_number decimal(8, 3),
    c_real real,
    c_small smallint,
    c_text text,
    c_ts timestamp,
    c_uuid uuid,
    c_xml xml,
    c_money money,
    c_inet inet,
    c_cidr cidr,
    c_macaddr macaddr
    );

insert into addax_tbl
values (999988887777,
        b'101',
        TRUE,
        '\xDEADBEEF',
        'hello',
        'hello, world',
        '2021-01-04',
        999888.9972,
        9876542,
        '{"bar": "baz", "balance": 7.77, "active": false}'::json,
        12345.123,
        123.123,
        126,
        'this is a long text ',
        '2020-01-04 12:13:14',
        'A0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A11'::uuid,
        '<foo>bar</foo>'::xml,
        '52093.89'::money,
        '192.168.1.1'::inet,
        '192.168.1/24'::cidr,
        '08002b:010203'::macaddr);

The statement to create the table to be inserted is as follows:

create table addax_tbl1 as select * from addax_tbl where 1=2;

Task Configuration

The following is the configuration file

{
  "job": {
    "setting": {
      "speed": {
        "byte": -1,
        "channel": 1
      }
    },
    "content": {
      "reader": {
        "name": "postgresqlreader",
        "parameter": {
          "username": "pgtest",
          "password": "pgtest",
          "column": [
            "*"
          ],
          "connection": {
            "table": [
              "addax_tbl"
            ],
            "jdbcUrl": "jdbc:postgresql://localhost:5432/pgtest"
          }
        }
      },
      "writer": {
        "name": "postgresqlwriter",
        "parameter": {
          "username": "pgtest",
          "password": "pgtest",
          "writeMode": "insert",
          "column": [
            "*"
          ],
          "preSql": [
            "truncate table @table"
          ],
          "connection": {
            "jdbcUrl": "jdbc:postgresql://127.0.0.1:5432/pgtest",
            "table": [
              "addax_tbl1"
            ]
          }
        }
      }
    }
  }
}

Save the above configuration file as job/pg2pg.json

Execute Collection Command

Execute the following command for data collection

bin/addax.sh job/pg2pg.json

Parameters

This plugin is based on RDBMS Writer, so you can refer to all configuration items of RDBMS Writer.

writeMode

By default, insert into syntax is used to write to PostgreSQL tables. If you want to use the mode of updating when primary key exists and inserting when it doesn't exist, you can use update mode. Assuming the table's primary key is id, the writeMode configuration method is as follows:

"writeMode": "update(id)"

If it's a composite unique index, the configuration method is as follows:

"writeMode": "update(col1, col2)"

Note: update mode was first added in version 3.1.6, previous versions do not support it.

Type Conversion

Currently PostgresqlWriter supports most PostgreSQL types, but there are also some cases that are not supported. Please check your types carefully.

The following lists PostgresqlWriter's type conversion list for PostgreSQL:

Addax Internal Type	PostgreSQL Data Type
Long	bigint, bigserial, integer, smallint, serial
Double	double precision, money, numeric, real
String	varchar, char, text, bit, inet,cidr,macaddr,uuid,xml,json
Date	date, time, timestamp
Boolean	bool
Bytes	bytea

Known Limitations

Except for the data types listed above, other data types are theoretically converted to string type, but accuracy is not guaranteed.