-- 導(dǎo)入時轉(zhuǎn)換數(shù)據(jù)類型
COPY users(id, name, reg_date) 
FROM '/data/users.csv' 
WITH (FORMAT csv, HEADER,
      DELIMITER '|',
      NULL 'NULL',
      FORCE_NOT_NULL (id, name),
      ENCODING 'UTF8');

-- 導(dǎo)出查詢結(jié)果
COPY (SELECT * FROM orders WHERE order_date > '2025-01-01') 
TO '/data/recent_orders.csv' 
WITH (FORMAT csv, HEADER);

# 使用并行加載（拆分文件后）
for i in {1..4}; do
  psql -c "COPY large_table FROM '/data/part$i.csv' WITH (FORMAT csv)" &
done
wait

-- 創(chuàng)建錯誤日志表
CREATE TABLE import_errors (
    line_num integer,
    error_msg text,
    raw_data text
);
-- 帶錯誤記錄的導(dǎo)入
BEGIN;
CREATE TEMP TABLE temp_import (LIKE target_table);
COPY temp_import FROM '/data/source.csv' 
  WITH (FORMAT csv, HEADER);
INSERT INTO target_table
  SELECT * FROM temp_import
  ON CONFLICT DO NOTHING;
INSERT INTO import_errors
  SELECT pg_copy_log();
COMMIT;

# 導(dǎo)出二進(jìn)制數(shù)據(jù)
pg_dump -t table_name -Fc -f output.dump dbname
# 二進(jìn)制文件轉(zhuǎn)換
pg_restore -l output.dump > output.list

-- 查看COPY操作歷史
SELECT query, duration 
FROM pg_stat_statements 
WHERE query LIKE 'COPY%' 
ORDER BY duration DESC;
-- 檢查導(dǎo)入進(jìn)度（PostgreSQL 14+）
SELECT pid, query, pg_stat_get_progress_info('COPY') 
FROM pg_stat_activity 
WHERE backend_type = 'client backend';