[PostgreSQL] 테이블 데이터 text 파일로 저장하기(COPY 명령)

PostgreSQL에서 테이블에 있는 데이터를 파일로 저장하고자 할 때 사용하는 COPY 유틸리티
테이블 <-> text 파일 모두 가능해서 유용하게 사용하는 경우가 종종 있습니다.
Oracle에서 text파일 데이터를 테이블에 적재할 때 사용하는 SQL*Loaderd와 비슷한 방식이라고 생각하면 됩니다.

 

 

COPY 명령

PSQL 커맨드라인에서 COPY 명령 실행

 

참고 사이트
https://www.postgresql.org/docs/current/sql-copy.html

-- 테이블 -> text파일
COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
    TO { 'filename' | PROGRAM 'command' | STDOUT }
    [ [ WITH ] ( option [, ...] ) ]

-- text파일 -> 테이블
COPY table_name [ ( column_name [, ...] ) ]
    FROM { 'filename' | PROGRAM 'command' | STDIN }
    [ [ WITH ] ( option [, ...] ) ]
    [ WHERE condition ]

 

Export (테이블 -> text파일)

text 타입 사용 시 구분자 옵션 안 주면 기본값인 Tab을 사용

파일 형식은 with 구문뒤에 옵션명에서 csv 또는 delimiter {컬럼구분자}를 주어 선택

 

csv 타입으로 copy 예시

copy tab1 to '/var/lib/pgsql/work/tab1.csv' with csv ;

 

text 타입으로 copy 예시

-- text 타입(구분자: ',' null: '')
copy tab1 to '/var/lib/pgsql/work/tab1.txt' with delimiter ',' null '' ;

-- text 타입(SELECT 문을 사용한 export)
copy (select * from tab1 limit 100)  to '/var/lib/pgsql/work/tab1_100.txt' delimiter ',' null '' ;

-- text 타입(특정 컬럼 데이터만 export)
copy tab1(userid,dbid) to '/var/lib/pgsql/work/tab1.txt' delimiter ',' null '' ;

 

파일 형식에 따른 컬럼 구분자(',') 출력 파일 비교

실제 데이터에 ',' 값이 들어가 있을 경우 컬럼구분자를 ','로 했을 때 타입별 출력 비교

 

테이블 조회

userid|dbid |toplevel|queryid            |query                                                                                                                                                                  
------+-----+--------+-------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    10|16384|true    |5745144006191083896|SELECT pc.oid,pc.relname,pc.relnamespace,pc.relkind FROM pg_catalog.pg_class pc WHERE pc.relkind in($2,$3,$4,$5,$6) AND pc.relname ILIKE $1 ORDER BY pc.relname LIMIT $7

 

csv 타입

10,16384,t,5745144006191083896,"SELECT pc.oid,pc.relname,pc.relnamespace,pc.relkind FROM pg_catalog.pg_class pc WHERE pc.relkind in($2,$3,$4,$5,$6) AND pc.relname ILIKE $1 ORDER BY pc.relname LIMIT $7"

 

text 타입(컬럼구분자 ',')

10,16384,t,5745144006191083896,SELECT pc.oid\,pc.relname\,pc.relnamespace\,pc.relkind FROM pg_catalog.pg_class pc WHERE pc.relkind in($2\,$3\,$4\,$5\,$6) AND pc.relname ILIKE $1 ORDER BY pc.relname LIMIT $7

 

null 옵션에 따른 null 데이터 출력 파일 비교

예시 데이터 c2='empty string'의 c1 값은 null 과는 다른 빈 문자열입니다.

 

테이블 조회

c1|c2          |
--+------------+
  |empty string|
  |is null     |

 

csv 타입 null 옵션 없음

-- copy tab2 to '/var/lib/pgsql/work/tab2.csv' with csv ;

"",empty string
,is null

 

 

text 타입 null 옵션 변경

-- copy tab2 to '/var/lib/pgsql/work/tab2_null.txt' with delimiter ',' null '' ;

,empty string
,is null

 

 

text 타입 null 옵션 없음(기본값)

-- copy tab2 to '/var/lib/pgsql/work/tab2.txt' with delimiter ',' ;

,empty string
\N,is null

 

원격지(remote)DB 데이터 로컬에 저장하기

클라이언트에서 psql로 접속 후 \copy 명령을 사용하면 로컬(노트북 등)에 다운받을 수 있다

postgres=> \copy tab1 to 'C:\pg_bak\tab1.txt' with delimiter ',' null '' ;
COPY 100
postgres=>

 

Import

컬럼 구분자를 가진 text 파일 → 테이블 적재 시 사용

import 시 기존 테이블에 데이터가 있다면 추가로 insert 함

test=# select count(*) from tab1 ;
 count
-------
     0
(1개 행)
 
test=# copy tab1 from '/var/lib/pgsql/work/tab1.csv' with csv ;
COPY 456
test=# select count(*) from tab1 ;
 count
-------
   456
(1개 행)
 
test=# copy tab1 from '/var/lib/pgsql/work/tab1.csv' with csv ;
COPY 456
test=# select count(*) from tab1 ;
 count
-------
   912
(1개 행)