Overview
This node executes PostgreSQL COPY commands to perform bulk data operations. Specifically, it supports exporting query results to CSV/TSV files (Copy To) and importing CSV/TSV files into PostgreSQL tables (Copy From). It is useful for efficiently transferring large datasets between PostgreSQL and external files, such as exporting query results for reporting or importing bulk data into a database table.
Use Case Examples
- Exporting data from a PostgreSQL table to a CSV file for analysis or sharing.
- Importing a CSV file containing user data into a PostgreSQL table for batch processing.
Properties
| Name | Meaning |
|---|---|
| Query | The SELECT SQL query to export data from the database. |
| Output Format | The file format for the exported data (CSV, TSV, or custom delimiter). |
| Custom Delimiter | The delimiter character to use when 'Custom Delimiter' output format is selected. |
| Include Header Row | Whether to include column names as the first row in the exported file. |
| File Name | The name of the output file to store the exported data. |
| Binary Property Name | The name of the binary property where the output file will be stored. |
| Options | Additional options for export such as quote character, null string representation, and file encoding. |
Output
Binary
The node outputs the exported file as binary data stored in a specified binary property, allowing downstream nodes to access the file content.
JSON
rowCount- Number of data rows exported (excluding header).fileSize- Size of the exported file in bytes.executionTimeMs- Time taken to execute the export operation in milliseconds.fileName- Name of the exported file.format- Format of the exported file (csv or tsv).
Dependencies
- PostgreSQL database connection using credentials including host, port, database, user, password, and SSL options.
- Node.js packages: pg (PostgreSQL client), pg-copy-streams (for COPY command streaming), and stream (for handling data streams).
Troubleshooting
- Ensure the PostgreSQL credentials are correct and the database is accessible to avoid connection errors.
- Verify the SQL query syntax in the 'Query' property to prevent query execution errors.
- If no binary data is found during import, check that the specified binary property name matches the input data.
- For import operations, ensure the input file format and delimiter settings match the actual file format to avoid parsing errors.
- Common error messages include connection failures, SQL syntax errors, and missing binary data; resolving these involves checking credentials, query correctness, and input data configuration.