Batch

type: "io.kestra.plugin.jdbc.postgresql.Batch"

Execute a batch query to a PostgresSQL server

Examples

Fetch rows from a table and bulk insert to another one

tasks:
  - id: query
    type: io.kestra.plugin.jdbc.postgresql.Query
    url: jdbc:postgresql://dev:56982/
    username: postgres
    password: pg_passwd
    sql: |
      SELECT *
      FROM xref
      LIMIT 1500;
    store: true
  - id: update
    type: io.kestra.plugin.jdbc.postgresql.Batch
    from: "{{ outputs.query.uri }}"
    url: jdbc:postgresql://prod:56982/
    username: postgres
    password: pg_passwd
    sql: |
      insert into xref values( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )

Properties

chunk

• Type: integer
• Dynamic: ✔️
• Required: ✔️

The size of chunk for every bulk request

columns

• Type: array
• SubType: string
• Dynamic: ✔️
• Required: ❌

The columns to be insert

If not provided, ? count need to match the from number of cols

from

• Type: string
• Dynamic: ✔️
• Required: ✔️

Source file URI

password

• Type: string
• Dynamic: ✔️
• Required: ❌

The database user's password

sql

• Type: string
• Dynamic: ✔️
• Required: ✔️

Insert query to be executed

The query must have as much question mark as column in the files.
Example: 'insert into database values( ? , ? , ? )' for 3 columns
In case you do not want all columns, you need to precise it in the query and in the columns property
Example: 'insert into(id,name) database values( ? , ? )' to select 2 columns

ssl

• Type: boolean
• Dynamic: ❌
• Required: ❌
• Default: false

Is the connection ssl

sslCert

• Type: string
• Dynamic: ✔️
• Required: ❌

The ssl cert

Must be a PEM encoded certificate

sslKey

• Type: string
• Dynamic: ✔️
• Required: ❌

The ssl key

Must be a PEM encoded key

sslKeyPassword

• Type: string
• Dynamic: ✔️
• Required: ❌

The ssl key password

sslMode

• Type: string

• Dynamic: ❌

• Required: ❌

• Possible Values:

  • DISABLE
  • ALLOW
  • PREFER
  • REQUIRE
  • VERIFY_CA
  • VERIFY_FULL

The ssl mode

sslRootCert

• Type: string
• Dynamic: ✔️
• Required: ❌

The ssl root cert

Must be a PEM encoded certificate

timeZoneId

• Type: string
• Dynamic: ❌
• Required: ❌

The time zone id to use for date/time manipulation. Default value is the worker default zone id.

url

• Type: string
• Dynamic: ✔️
• Required: ✔️

The jdbc url to connect to the database

username

• Type: string
• Dynamic: ✔️
• Required: ❌

The database user

Outputs

rowCount

• Type: integer

The rows count

updatedCount

• Type: integer

The updated rows count