PostgreSQL COPY moves data between a table and a supported input or output path. It can make bulk import and export efficient, but the file location, format, and privileges determine a substantial trust boundary. Server-side COPY and a client’s copy workflow are not interchangeable.
A dependable import defines the source, accepted rows, transaction behavior, and final publication step. This guide explains format choices, staging, permissions, and verification so high throughput does not bypass the application’s data-quality and access requirements.
Define the bulk operation’s contract
Identify the destination table, intended columns, source owner, and expected volume. Do not let an arbitrary uploaded filename select a database table or server path. Authorization belongs before the bulk operation begins.
State whether the input contains new records, replacements, or data for later reconciliation. COPY is not a universal conflict-resolution workflow. An import with update semantics may need validated staging and a separate reviewed merge.
Define success beyond the command finishing. Expected row count, required fields, domain rules, and application visibility are distinct checks. A large accepted count can still represent the wrong source or column mapping.
Distinguish server files from client files
COPY with a filename reads or writes from the database server’s filesystem under the server process’s access. A path on an administrator’s laptop is not automatically a path the server can use.
The psql backslash-copy workflow uses supported COPY streaming through the client, with file access occurring on the client side. This changes the file-access boundary but does not remove database authorization requirements.
Choose the least powerful supported workflow that meets the need. Granting server-file access just to load a client-owned CSV can expose much more authority than the import requires.
Avoid broad PROGRAM authority
COPY PROGRAM runs a command on the server under documented privileges. It is not merely a convenient way for the client to preprocess a file. Treat that execution path as consequential server authority.
Never build a PROGRAM string from uncontrolled user input. Even approved programs need safe argument handling, resource limits, and appropriate permissions. The import should not become an arbitrary command-execution interface.
Use a controlled client preprocessing or streaming design where it is sufficient. Review server-file and program roles carefully; they can provide access beyond one table. A narrow database task should not inherit broad host capabilities by convenience.
Make columns and formats explicit
Specify the intended columns and supported format. CSV quoting, delimiters, null representation, headers, encoding, and date interpretation need agreement between producer and consumer. Similar-looking files can have different semantics.
Test values containing commas, quotes, newlines, empty strings, and nulls. An empty field and a missing value may not mean the same thing under the selected options. Preserve the business distinction deliberately.
Binary format has its own compatibility considerations. Do not assume it is a universal interchange format for every tool and server version. Choose format from the controlled producer-consumer contract.
Validate through an owned staging workflow
For untrusted or complex imports, load into an appropriate staging area and validate before making rows authoritative. Keep staging access restricted and tie records to a controlled import identity.
Check domain constraints, duplicates, referenced objects, and tenant ownership. A row’s syntax can be valid while its business meaning is wrong. Do not merge staged data solely because it was accepted by the parser.
Bound staging retention and storage. Rejected files and partial imports can accumulate private data. Cleanup should follow an owned policy and preserve only the evidence required for correction or audit.
Understand constraints, triggers, and failure policy
COPY interacts with table constraints and triggers according to PostgreSQL’s documented behavior. Review row-security and supported destination restrictions for the actual role and server version. Do not treat it as a bypass around normal validation.
Choose error behavior deliberately. Version-specific options for handling conversion errors or rejected rows need careful review. Skipping bad rows can be useful in a defined workflow, but it must not silently turn an incomplete import into success.
Record accepted and rejected outcomes in a meaningful way. A file intended to replace a complete dataset may require all-or-nothing acceptance, while another workflow may permit reviewed partial intake.
Keep transaction and external work coordinated
Define who owns the transaction around staging and final updates. A helper that commits early can destroy an intended atomic publication boundary. Test a failure during each important phase.
An unsuccessful bulk load can have storage and maintenance consequences even when rows are not visible as accepted data. Review the documented cleanup and vacuum considerations rather than repeatedly loading huge failed files without monitoring.
Do not perform irreversible external actions for every input row before database acceptance is established. A later rollback does not unsend messages or undo another system’s update. Coordinate those effects through an appropriate durable workflow.
Protect exports as carefully as imports
COPY TO can produce a concentrated extract of private data. Apply the intended row and column scope, role permissions, destination access, and retention policy. Export throughput is not an exemption from data governance.
Keep credentials and sensitive record content out of command logs and broad diagnostics. Use controlled import identifiers and summarized failure categories. Error samples should be minimized and shared only with the appropriate audience.
Verify the resulting file’s format and completeness before handing it off. A successful export command does not prove a downstream parser interprets the fields or null values correctly.
Rehearse recovery with representative data
Test valid input, malformed rows, duplicates, interrupted transfer, permission denial, and a failure in the final merge. Verify final tables and staging state for each path. Counts alone are insufficient when the wrong values can have the right count.
Measure throughput, lock impact, disk use, and application behavior under an approved realistic workload. A fast local import into an empty unconstrained table may not predict production performance.
For a customer import, stream an approved file into restricted staging, validate ownership and fields, merge through an owned transaction, and publish only after acceptance. That sequence makes bulk speed compatible with a clear trust boundary.
Frequently asked questions
Does a COPY filename refer to the client machine?
Not for server-side COPY. Use the documented client streaming workflow when appropriate.
Is COPY PROGRAM a harmless file shortcut?
No. It executes a server-side command and requires a careful authority review.
Where are formats and privileges documented?
Read the PostgreSQL COPY reference for the actual server version.
For a complementary workflow, read PostgreSQL UPSERT: Conflicts, Keys and Update Meaning.
