Beginner LevelPostgreSQL
How does the COPY command execute high-speed CSV file import and export?
❓ Interview Prompt: Explain COPY command, streaming raw binary/CSV data directly to table pages, bypassing SQL parser overhead, and \copy in psql.
The 'COPY' command streams bulk data directly between files and PostgreSQL storage engine pages, bypassing SQL parser and query planner overhead:
•
'COPY table FROM 'file.csv'': Server-side copy (file must exist on database server disk).
•
'\copy table FROM 'file.csv'': Client-side psql meta-command (streams file from local client machine over network!).
Code Implementation Example
sql
-- Bulk import CSV file directly into PostgreSQL table
COPY orders(id, customer_name, total_amount, created_at)
FROM '/var/lib/postgresql/data/orders_bulk.csv'
WITH (FORMAT csv, HEADER true, DELIMITER ',');💡Bulk importing CSV data using the server-side COPY command.
🎯 Interview Key Takeaway
Use the COPY command (or \copy in psql) for bulk importing large CSV datasets 10x faster than INSERT statements.
#COPY#Bulk Import#CSV#Performance#Beginner
Back to all PostgreSQL Questions→