PostgreSQL COPY Command: The Ultimate Guide to Import & Export Data [2024]

• What is PostgreSQL COPY Command?
• When to Use COPY Command?
• COPY Command Syntax
• Step-by-Step Examples
• Performance Tips
• Troubleshooting Guide

What is PostgreSQL COPY Command?

Key Benefits:

• 🚀 Faster than INSERT statements
• 📦 Efficient bulk data handling
• 🔄 Bi-directional data transfer
• 📊 Support for multiple file formats

When to Use COPY Command?

Perfect for:

• Large dataset imports
• Regular data backups
• Data migration projects
• ETL processes
• Performance-critical operations

Not recommended for:

• Small data insertions
• Complex data transformations
• Real-time data operations

COPY Command Syntax

Basic Syntax

-- Export data to CSV
COPY table_name [(column_list)]
TO 'file_path'
[WITH] (option = 'value');

-- Import data from CSV
COPY table_name [(column_list)]
FROM 'file_path'
[WITH] (option = 'value');

Popular Format Options

-- CSV Export with common options
COPY users TO '/tmp/users.csv' WITH (
    FORMAT CSV,
    HEADER true,
    DELIMITER ',',
    QUOTE '"',
    ENCODING 'UTF8'
);

Step-by-Step Examples

1. Export Entire Table

-- Export complete table with headers
COPY employees TO '/tmp/employees_full.csv' 
WITH (FORMAT CSV, HEADER);

2. Import Data with Custom Delimiters

-- Import tab-delimited data
COPY employees FROM '/tmp/employees.tsv' 
WITH (
    FORMAT CSV,
    DELIMITER E'\t',
    HEADER
);

3. Export Filtered Data

-- Export specific data based on conditions
COPY (
    SELECT id, name, email 
    FROM users 
    WHERE created_at >= '2024-01-01'
) TO '/tmp/new_users_2024.csv' 
WITH (FORMAT CSV, HEADER);

Performance Tips

1. Optimize for Bulk Import

-- Disable triggers and indexes temporarily
BEGIN;
ALTER TABLE employees DISABLE TRIGGER ALL;
DROP INDEX IF EXISTS idx_employees_email;

COPY employees FROM '/tmp/bulk_data.csv' WITH (FORMAT CSV, HEADER);

-- Recreate indexes and enable triggers
CREATE INDEX idx_employees_email ON employees(email);
ALTER TABLE employees ENABLE TRIGGER ALL;
COMMIT;

2. Memory Management

-- Set work_mem for large operations
SET work_mem = '256MB';

3. Batch Processing

-- Use transactions for large datasets
BEGIN;
COPY employees FROM '/tmp/batch1.csv' WITH (FORMAT CSV);
COPY employees FROM '/tmp/batch2.csv' WITH (FORMAT CSV);
COMMIT;

Troubleshooting Guide

Common Error: Permission Denied

# Solution: Set correct file permissions
sudo chown postgres:postgres /tmp/data.csv
chmod 644 /tmp/data.csv

Common Error: Invalid UTF-8 Encoding

-- Solution: Specify encoding explicitly
COPY table_name FROM '/tmp/data.csv' 
WITH (FORMAT CSV, ENCODING 'UTF8');

Common Error: Column Mismatch

-- Solution: Specify columns explicitly
COPY users (id, name, email) 
FROM '/tmp/users.csv' WITH (FORMAT CSV, HEADER);

Best Practices for Data Security

1. File Access Control

-- Grant minimal required permissions
GRANT COPY ON employees TO import_user;

2. Data Validation

-- Validate data before import
BEGIN;
CREATE TEMP TABLE temp_import (LIKE employees);
COPY temp_import FROM '/tmp/data.csv' WITH (FORMAT CSV, HEADER);
-- Validate data here
INSERT INTO employees SELECT * FROM temp_import;
COMMIT;

Advanced Use Cases

1. Daily Automated Exports

-- Create timestamp-based exports
\set filename '/backup/users_' :date '.csv'
COPY users TO :'filename' WITH (FORMAT CSV, HEADER);

2. Data Transformation During Export

COPY (
    SELECT 
        id,
        UPPER(name) as name,
        email,
        TO_CHAR(created_at, 'YYYY-MM-DD') as join_date
    FROM users 
    WHERE active = true
) TO '/tmp/active_users.csv' WITH (FORMAT CSV, HEADER);

FAQs About PostgreSQL COPY Command

Q: How fast is COPY compared to INSERT?

Q: Can COPY handle JSON data?

Q: What's the maximum file size for COPY?

Conclusion and Next Steps

• Optimize your data transfer operations
• Avoid common pitfalls
• Implement best practices for your use case