Use the COPY command to load large external files directly into a PostgreSQL table with minimal logging and maximal speed.
INSERT runs one row at a time and writes full WAL; COPY streams data straight to disk, batching commits and cutting write-ahead logging. Expect 10-100× speed-ups for million-row loads.
You need superuser or the role with the pg_read_server_files privilege to read server-side files. For \copy from a client workstation, ordinary INSERT rights on the target table are enough.
COPY supports CSV, text (delimited), binary, and program output via STDIN. CSV is safest for cross-platform workflows and supports NULL & quote handling.
Products?1. Place products.csv on the server.
2. Run the COPY command shown below.
3. Verify row count with SELECT COUNT(*) FROM Products;
Format your CSV columns exactly as PostgreSQL expects (ISO-8601 dates, unformatted numbers). Alternatively, load into a staging table of text columns, then cast into the final schema.
Disable indexes and foreign keys until after the load, set maintenance_work_mem high, and use ALTER TABLE ... SET UNLOGGED for transient staging tables.
COPY Products(id, name, price, stock)FROM '/var/lib/postgresql/import/products.csv'WITH (FORMAT csv, HEADER true, DELIMITER ',', QUOTE '"');
Use \copy in psql. The client reads the file and streams it to the server, bypassing the need for server-side file access.
Typical failures include bad delimiters, wrong column counts, or data-type mismatches. Load a sample with LIMIT first, or add LOG ERRORS (pg-exttable) to trap bad rows.
• Validate the file schema.
• Load into staging.
• Analyze the table after load.
• Re-enable constraints and indexes.
• Vacuum if you used UNLOGGED.
No. COPY is one transaction. Use smaller files or pg_copy wrappers if you need chunked commits.
Add HEADER true in the WITH clause when loading CSV files.
Yes, but consider SET session_replication_role = replica offloading if you are replicating logical changes elsewhere.
.avif)
.avif){kind=logo}
