PostgreSQL CopyOut

PostgreSQL CopyOut

Certified

Copy tabular data from a PostgreSQL table to a file

Exports tabular data from a PostgreSQL table or query to a file using the COPY command.

yaml
type: io.kestra.plugin.jdbc.postgresql.CopyOut

Export a PostgreSQL table or query to a CSV or TSV file.

yaml
id: postgres_copy_out
namespace: company.team

tasks:
  - id: copy_out
    type: io.kestra.plugin.jdbc.postgresql.CopyOut
    url: jdbc:postgresql://sample_postgres:5432/world
    username: "{{ secret('POSTGRES_USERNAME') }}"
    password: "{{ secret('POSTGRES_PASSWORD') }}"
    format: CSV
    sql: SELECT 1 AS int, 't'::bool AS bool UNION SELECT 2 AS int, 'f'::bool AS bool
    header: true
    delimiter: "\t"

Export output of a Postgres SQL query to a CSV file

yaml
id: export_from_postgres
namespace: company.team

tasks:
  - id: export
    type: io.kestra.plugin.jdbc.postgresql.CopyOut
    url: jdbc:postgresql://sample_postgres:5432/world
    username: "{{ secret('POSTGRES_USERNAME') }}"
    password: "{{ secret('POSTGRES_PASSWORD') }}"
    format: CSV
    header: true
    sql: SELECT * FROM country LIMIT 10
    delimiter: ","

  - id: log
    type: io.kestra.plugin.core.log.Log
    message: "{{ outputs.export.rowCount }}"
Properties

The JDBC URL to connect to the database

SubTypestring

An optional list of columns to be copied

If no column list is specified, all columns of the table will be copied.

Specifies the character that separates columns within each row (line) of the file

The default is a tab character in text format, a comma in CSV format. This must be a single one-byte character. This option is not allowed when using binary.

Specifies that the file is encoded in the encoding_name

If this option is omitted, the current client encoding is used. See the Notes below for more details.

Specifies the character that should appear before a data character that matches the QUOTE value

The default is the same as the QUOTE value (so that the quoting character is doubled if it appears in the data). This must be a single one-byte character. This option is allowed only when using CSV format.

SubTypestring

Do not match the specified columns' values against the null string

In the default case where the null string is empty, this means that empty values will be read as zero-length strings rather than nulls, even when they are not quoted. This option is allowed only in COPY FROM, and only when using CSV format.

SubTypestring

Match the specified columns' values against the null string, even if it has been quoted, and if a match is found set the value to NULL

In the default case where the null string is empty, this converts a quoted empty string into NULL. This option is allowed only in COPY FROM, and only when using CSV format.

SubTypestring

Forces quoting to be used for all non-NULL values in each specified column

NULL output is never quoted. If * is specified, non-NULL values will be quoted in all columns. This option is allowed only in COPY TO, and only when using CSV format.

DefaultTEXT
Possible Values
TEXTCSVBINARY

Selects the data format to be read or written

Requests copying the data with rows already frozen, just as they would be after running the VACUUM FREEZE command

This is intended as a performance option for initial data loading. Rows will be frozen only if the table being loaded has been created or truncated in the current sub-transaction, there are no cursors open and there are no older snapshots held by this transaction. It is currently not possible to perform a COPY FREEZE on a partitioned table.

Note that all other sessions will immediately be able to see the data once it has been successfully loaded. This violates the normal rules of MVCC visibility and users specifying should be aware of the potential problems this might cause.

Specifies that the file contains a header line with the names of each column in the file

On output, the first line contains the column names from the table, and on input, the first line is ignored. This option is allowed only when using CSV.

Specifies the string that represents a null value

The default is \N (backslash-N) in text format, and an unquoted empty string in CSV format. You might prefer an empty string even in text format for cases where you don't want to distinguish nulls from empty strings. This option is not allowed when using binary format.

Specifies copying the OID for each row

An error is raised if OIDs is specified for a table that does not have OIDs, or in the case of copying a query.

The database user's password

Reference (ref) of the pluginDefaults to apply to this task.

Specifies the quoting character to be used when a data value is quoted

The default is double-quote. This must be a single one-byte character. This option is allowed only when using CSV format.

The timeout value in seconds used for socket read operations

If reading from the server takes longer than this value, the connection is closed. A value of zero means the timeout is disabled. Default is zero (disabled).

A SELECT, VALUES, INSERT, UPDATE or DELETE command whose results are to be copied

For INSERT, UPDATE and DELETE queries a RETURNING clause must be provided, and the target relation must not have a conditional rule, nor an ALSO rule, nor an INSTEAD rule that expands to multiple statements.

Defaultfalse

Is the connection SSL?

The SSL cert

Must be a PEM encoded certificate

The SSL key

Must be a PEM encoded key

The SSL key password

Possible Values
DISABLEALLOWPREFERREQUIREVERIFY_CAVERIFY_FULL

The SSL mode

The SSL root cert

Must be a PEM encoded certificate

The name (optionally schema-qualified) of an existing table

The database user

The rows count from this COPY

Formaturi

The URI of the result file on Kestra's internal storage

Unitrows

The number of rows copied from PostgreSQL.

More Plugins in this Category

Clickhouse
Clickhouse

Tasks that connect to ClickHouse via JDBC to run queries and loads.

Data

5 tasks

2 blueprints

Weaviate
Weaviate

Tasks that manage and query vectors in Weaviate.

DataAI

4 tasks

2 blueprints