COPY ... FROM '/path/file.csv' 는 서버 프로세스가 서버의 파일 시스템에서 읽는다. 슈퍼유저이거나 pg_read_server_files 롤이어야 하고, 경로도 서버 기준이다.
\copy 는 psql 메타 명령으로 클라이언트가 파일을 읽어 서버로 흘려보낸다. 일반 권한으로 되고 경로는 클라이언트 기준이다. 운영에서는 보통 이쪽을 쓴다.
psql -h dbhost -U appuser -d appdb \
-c "\copy app.users(id, name, email) FROM '/home/ops/users.csv' WITH (FORMAT csv, HEADER true)"
CSV 한 줄의 필드 수가 대상 컬럼 수보다 많다는 뜻이다. 원인은 대개 셋이다.
값 안의 쉼표가 따옴표로 감싸이지 않았다. 아래 첫 줄은 필드가 넷으로 해석된다.
1,홍길동,서울시 강남구, 테헤란로,010-0000-0000
1,홍길동,"서울시 강남구, 테헤란로",010-0000-0000
구분자가 쉼표가 아닌데 기본값으로 읽었다. 탭이나 파이프라면 명시한다.
\copy app.users FROM 'users.tsv' WITH (FORMAT csv, DELIMITER E'\t', HEADER true)
줄 끝이 CRLF 인데 필드에 캐리지 리턴이 섞여 들어갔다. 파일을 먼저 확인한다.
file users.csv
head -c 200 users.csv | od -c | head
sed -i 's/\r$//' users.csv
반대로 필드 수가 모자란다. 대상 컬럼 목록을 명시하면 CSV 에 없는 컬럼은 기본값이나 NULL 로 채워진다.
\copy app.users(id, name) FROM 'users.csv' WITH (FORMAT csv, HEADER true)
빈 값을 NULL 로 넣고 싶으면 옵션을 준다. CSV 형식에서 따옴표 없는 빈 필드는 기본적으로 NULL 로 해석되지만, 따옴표로 감싼 빈 문자열("")은 빈 문자열이다.
\copy app.users FROM 'users.csv' WITH (FORMAT csv, HEADER true, NULL '')
NOT NULL 제약이 있는 컬럼이 비면 여기서 걸린다.
file -i users.csv
iconv -f CP949 -t UTF-8 users.csv -o users.utf8.csv
COPY 문에서 파일 인코딩을 지정할 수도 있다.
\copy app.users FROM 'users.csv' WITH (FORMAT csv, HEADER true, ENCODING 'EUC_KR')
예전에는 오류가 나면 전체 COPY 가 실패하는 것 말고는 방법이 없었다. PostgreSQL 17 부터 ON_ERROR 옵션이 생겼다.
COPY app.users FROM '/data/users.csv' WITH (FORMAT csv, HEADER true, ON_ERROR ignore);
건너뛴 행을 로그로 남기는 LOG_VERBOSITY 와 허용 한도를 정하는 REJECT_LIMIT 도 있다. 어느 옵션까지 쓸 수 있는지는 서버 버전에 따라 다르므로 확인한다.
16 이하에서는 전부 텍스트 컬럼인 임시 테이블에 먼저 넣고 SQL 로 걸러 옮기는 방법이 확실하다.
CREATE TEMP TABLE stg_users (id text, name text, email text);
\copy stg_users FROM 'users.csv' WITH (FORMAT csv, HEADER true)
INSERT INTO app.users (id, name, email)
SELECT id::bigint, name, email
FROM stg_users
WHERE id ~ '^[0-9]+$';
인터넷 예시에서 보이는 LOG ERRORS INTO ... SEGMENT REJECT LIMIT 은 Greenplum 문법이며 PostgreSQL 에서는 동작하지 않는다.
psql -U appuser -d appdb -f /home/ops/load.sql
psql 안에서는 \i /home/ops/load.sql 을 쓴다. 오류가 나면 멈추게 하려면 다음을 준다.
psql -v ON_ERROR_STOP=1 -U appuser -d appdb -f load.sql
-f 없이 < 로 넘기면 각 문장이 따로 처리되고 종료 코드로 실패를 구분하기 어렵다. 스크립트에서는 -f 와 ON_ERROR_STOP=1 을 함께 쓴다. 파일 전체를 한 트랜잭션으로 묶으려면 --single-transaction 을 더한다.