Query a PostgreSQL database.
type: "io.kestra.plugin.jdbc.postgresql.Query"
Examples
Execute a query and fetch results in a task, and update another table with fetched results in a different task.
id: postgres_query
namespace: company.team
tasks:
- id: fetch
type: io.kestra.plugin.jdbc.postgresql.Query
url: jdbc:postgresql://127.0.0.1:56982/
username: "{{ secret('POSTGRES_USERNAME') }}"
password: "{{ secret('POSTGRES_PASSWORD') }}"
sql: select concert_id, available, a, b, c, d, play_time, library_record, floatn_test, double_test, real_test, numeric_test, date_type, time_type, timez_type, timestamp_type, timestampz_type, interval_type, pay_by_quarter, schedule, json_type, blob_type from pgsql_types
fetchType: FETCH
- id: use_fetched_data
type: io.kestra.plugin.jdbc.postgresql.Query
url: jdbc:postgresql://127.0.0.1:56982/
username: "{{ secret('POSTGRES_USERNAME') }}"
password: "{{ secret('POSTGRES_PASSWORD') }}"
sql: "{% for row in outputs.fetch.rows %} INSERT INTO pl_store_distribute (year_month,store_code, update_date) values ({{row.play_time}}, {{row.concert_id}}, TO_TIMESTAMP('{{row.timestamp_type}}', 'YYYY-MM-DDTHH:MI:SS.US') ); {% endfor %}"
Properties
url *Requiredstring
The JDBC URL to connect to the database.
fetchSize integerstring
10000
Number of rows that should be fetched.
Gives the JDBC driver a hint as to the number of rows that should be fetched from the database when more rows are needed for this ResultSet object. If the fetch size specified is zero, the JDBC driver ignores the value and is free to make its own best guess as to what the fetch size should be. Ignored if autoCommit
is false.
fetchType string
NONE
STORE
FETCH
FETCH_ONE
NONE
The way you want to store data.
FETCH_ONE - output the first row. FETCH - output all rows as output variable. STORE - store all rows to a file. NONE - do nothing.
parameters object
Parameters
A map of parameters to bind to the SQL queries. The keys should match the parameter placeholders in the SQL string, e.g., : parameterName.
password string
The database user's password.
sql string
The SQL query to run.
ssl booleanstring
false
Is the connection SSL?
sslCert string
The SSL cert.
Must be a PEM encoded certificate
sslKey string
The SSL key.
Must be a PEM encoded key
sslKeyPassword string
The SSL key password.
sslMode string
DISABLE
ALLOW
PREFER
REQUIRE
VERIFY_CA
VERIFY_FULL
The SSL mode.
sslRootCert string
The SSL root cert.
Must be a PEM encoded certificate
timeZoneId string
The time zone id to use for date/time manipulation. Default value is the worker's default time zone id.
username string
The database user.
Outputs
row object
Map containing the first row of fetched data.
Only populated if fetchOne
parameter is set to true.
rows array
List of map containing rows of fetched data.
Only populated if fetch
parameter is set to true.
size integer
The number of rows fetched.
Only populated if store
or fetch
parameter is set to true.
uri string
uri
The URI of the result file on Kestra's internal storage (.ion file / Amazon Ion formatted text file).
Only populated if store
is set to true.