PostgreSQL COPY — Fast CSV Import

-- Basic COPY FROM (server-side, file on DB server)
COPY orders (id, customer_id, amount, status, created_at)
FROM '/data/orders.csv'
WITH (
    FORMAT csv,
    HEADER true,
    DELIMITER ',',
    NULL '',
    QUOTE '"',
    ESCAPE '"'
);

-- Client-side copy (\copy from psql — file on client machine)
-- \copy orders FROM 'local_file.csv' CSV HEADER

-- COPY to staging table, then merge
CREATE TEMP TABLE staging_orders (LIKE orders INCLUDING DEFAULTS);

COPY staging_orders FROM '/data/orders.csv' CSV HEADER;

-- Upsert from staging
INSERT INTO orders
SELECT * FROM staging_orders
ON CONFLICT (id) DO UPDATE SET
    amount = EXCLUDED.amount,
    status = EXCLUDED.status,
    updated_at = NOW();

-- Export: COPY TO
COPY (
    SELECT id, email, created_at
    FROM users
    WHERE created_at >= '2024-01-01'
)
TO '/tmp/users_export.csv'
WITH (FORMAT csv, HEADER true);

-- Check row count after import
SELECT COUNT(*) AS imported_rows FROM orders;

-- Performance: COPY is typically 5-10x faster than INSERT
-- For very large loads, consider:
--   1. Drop indexes before COPY, rebuild after
--   2. Increase maintenance_work_mem
--   3. Use UNLOGGED tables for staging