Postgres COPY

Execute PostgreSQL COPY commands for bulk operations
Join our community

Actions2

Overview

This node executes PostgreSQL COPY commands to perform bulk data operations. It supports two main operations: 'Copy To (Export)' which exports query results to CSV/TSV files, and 'Copy From (Import)' which imports CSV/TSV files into a specified database table. This node is useful for efficiently transferring large datasets between PostgreSQL and external files, such as exporting query results for reporting or importing bulk data from files into a database table.

Use Case Examples

  1. Exporting data from a PostgreSQL table to a CSV file for analysis or backup.
  2. Importing a CSV file containing user data into a PostgreSQL table for batch processing.

Properties

Name Meaning
Table Name Specifies the target table name for importing data.
Input Binary Field Binary field containing the file to import.
Input Format Format of the input file (CSV, TSV, or Custom Delimiter).
Custom Delimiter Custom delimiter character used if input format is custom.
Has Header Row Indicates if the first row contains column names.
Column Mapping Maps input columns to target table columns; order matters.
Options Additional import options such as quote character, null string, skip errors, and dry run mode.

Output

Binary

Binary output contains the imported or exported file data when exporting (copyTo operation).

JSON

  • rowCount - Number of rows processed during export.
  • fileSize - Size of the exported file in bytes.
  • executionTimeMs - Execution time in milliseconds for the operation.
  • fileName - Name of the exported file.
  • format - Format of the exported file (csv or tsv).
  • success - Indicates if the import operation was successful.
  • table - Target table name for import.
  • rowsImported - Number of rows successfully imported.
  • rowsSkipped - Number of rows skipped due to errors.
  • errors - List of errors encountered during import.
  • dryRun - Indicates if the import was a dry run (no actual data imported).

Dependencies

  • PostgreSQL database
  • PostgreSQL client library
  • pg-copy-streams for COPY command streaming

Troubleshooting

  • Ensure the binary field specified for import contains valid base64-encoded file data; otherwise, an error 'No binary data found in property' will be thrown.
  • Verify that the target table exists and the column mapping matches the table schema to avoid import errors.
  • If the COPY command fails, check PostgreSQL permissions and connection credentials.
  • For dry run mode, note that no data is actually imported; use this to validate file format and data correctness before actual import.

Links

Discussion