[PostgreSQL] COPY

COPY -- copy data between a file and a table

copy란 말 그대로 파일과테이블 사이 간 데이터를 복사를 해주는 function입니다.

시놉시스

COPY table_name [ ( column [, ...] ) ] FROM { 'filename' | STDIN } [ [ WITH ] ( option [, ...] ) ] COPY { table_name [ ( column [, ...] ) ] | ( query ) } TO { 'filename' | STDOUT } [ [ WITH ] ( option [, ...] ) ] where option can be one of: FORMAT format_name OIDS [ boolean ] DELIMITER 'delimiter_character' NULL 'null_string' HEADER [ boolean ] QUOTE 'quote_character' ESCAPE 'escape_character' FORCE_QUOTE { ( column [, ...] ) | * } FORCE_NOT_NULL ( column [, ...] ) ENCODING 'encoding_name'

설명

COPY는 PostgreSQL의 테이블과 표준 파일 시스템 파일 간에 데이터를 이동을 도와줍니다. COPY TO는 테이블의 내용을 복사해 테이블에 전달 하는 함수입니다. COPY FROM은 파일 내용을 복사해 테이블에 전달합니다. (테이블이 이미 데이터가 존재하면 데이터를 덧붙입니다.) 또한 COPY는 SELECT 쿼리의 결과를 복사 할 수 있습니다. 만약 컬럼의 리스트를 구체화한다면, COPY는 파일에서 또는 파일로부터 구체화된 컬럼의 데이터만 복사합니다. 만약 컬럼 리스트에 없는 테이블의 컬럼이 존재할 경우, COPY FROM은 해당 컬럼을 위해 기본값을 insert합니다. 파일 이름을 가진 COPY는 PostgreSQL서버에 직접적으로 파일을 읽거나 쓰는 것을 지시합니다. 이 파일은 서버에 액세스 할 수 있어야하고 이름은 서버의 관점에서 지정해야합니다. STDIN 또는 STDOUT이 구체화된다면, 데이터는 클라이언트와 서버 사이의 연결을 통해 전송됩니다.

파라미터

table_name

존재하는 테이블 이름

column

복사할 컬럼의 리스트 만약 컬럼리스트를 구체화한 것이 없으면 테이블의 모든 컬럼을 복사

query

SELECT 또는 VALUES 명령어의 결과를 복사할 수 있습니다. 이 경우 쿼리를 괄호로 감싸야 합니다.

filename

인풋 또는 아웃풋 파일의 절대경로. 윈도우 유저는 ''문자열을 사용해야 하며 파일 경로이름에 \\ (더블 백슬래시)를 사용해야합니다.

STDIN

클라이언트 어플리케이션으로부터 인풋을 구체화 받습니다. 

STDOUT

클라이언트 어플리케이션에 아웃풋을 구체화 합니다.

boolean

선택된 옵션을 킬지 끌지를 구체화합니다. 만약 TRUE라면 on 또는 1이며 옵션을 사용하겠다는 의미입니다. 반대로 FALSE라면 off 또는 0이며 사용하지 않겠다는 것입니다. 이 파라미터는 생략할 수 있으며, 생략할 시 TRUE가 기본값입니다.

FORMAT

어떤 포맷으로 읽을지 또는 쓸지에 대해 선택을 합니다.  text, csv (콤마로 값을 구분), binary. 기본은 text

OIDS

각 row를 위한 OID 복사를 구체화합니다. (만약 OIDs를 가지고 있지 않은 테이블을 구체화 하거나 copy하고자 query를 만들면 에러가 발생합니다.)

DELIMITER

파일의 각 row (line) 안에 컬럼들을 분리하는 문자를 나타냅니다. 기본값은 text 포맷에서 tab 문자이고 csv 포맷에서는 콤마입니다. 이 delimiter는 1바이트 문자열만 가능합니다. binary 포맷에서는 이 옵션을 허용하지 않습니다.

NULL

null값인 문자열을 구체화합니다. 기본값은 텍스트 포맷에서 \N(백슬래시 N), csv 포맷에서 인용되지 않은 문자열 (unquoted empty string)입니다. text 포맷에서 텅빈 문자열과 null값을 구분하는 것을 원하지 않아 텅 빈 문자열을 선호할 수있습니다. binary 포맷은 사용할 수 없습니다.

HEADER

파일안에 각 컬럼의 이름을 가지는 헤더 라인을 포함하도록 구체화합니다. 아웃풋에서 첫 라인은 테이블로부터의 컬럼이름, input을 포함하고 첫 라인은 무시됩니다. 이 옵션은 csv 포맷에서만 허용됩니다.

QUOTE

데이터 값이 인용될 때 인용 문자(quoting character)가 사용되도록 지정합니다. 기본값은 큰 따옴표(double-quote)입니다. 이것은 반드시 1바이트 문자열이여야 합니다. 이 옵션은 csv 포맷일떄만 허용됩니다.

ESCAPE

Specifies the character that should appear before a data character that matches the QUOTE value. The default is the same as the QUOTE value (so that the quoting character is doubled if it appears in the data). This must be a single one-byte character. This option is allowed only when using CSV format.

FORCE_QUOTE

Forces quoting to be used for all non-NULL values in each specified column. NULL output is never quoted. If * is specified, non-NULL values will be quoted in all columns. This option is allowed only in COPY TO, and only when using CSV format.

FORCE_NOT_NULL

Do not match the specified columns' values against the null string. In the default case where the null string is empty, this means that empty values will be read as zero-length strings rather than nulls, even when they are not quoted. This option is allowed only in COPY FROM, and only when using CSV format.

ENCODING

Specifies that the file is encoded in the encoding_name. If this option is omitted, the current client encoding is used. See the Notes below for more details.

COPY TO

copy emp to '~/path/to/emp1.csv'; 7369 SMITH CLERK 7902 1980-12-17 800 \N 20 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 7566 JONES MANAGER 7839 1981-04-02 2975 \N 20 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30   copy emp(empno,ename) to '~/path/to/emp2.csv'; 7369 SMITH 7499 ALLEN 7521 WARD 7566 JONES 7654 MARTIN   copy emp(empno,ename) to '~/path/to/emp3.csv' with delimiter ',' csv header empno,ename 7369,SMITH 7499,ALLEN 7521,WARD 7566,JONES 7654,MARTIN

COPY FROM

emp3.csv   empno,ename 7369,SMITH 7499,ALLEN 7521,WARD 7566,JONES 7654,MARTIN copy customer(no,name) from '~/path/to/emp3.csv' with delimiter ',' csv header select * from customer; no | name ------+-------- 7369 | SMITH 7499 | ALLEN 7521 | WARD 7566 | JONES 7654 | MARTIN

필드값 내에 ","가 들어가 있을 경우

a.csv 1,2,"super,man","spider","man" copy ccc(a,b,c,d,e) from '~/path/to/a.csv' with csv quote '"' select * from ccc; a | b | c | d | e ---+---+-----------+--------+----- 1 | 2 | super,man | spider | man 컬럼변환이 필요할 때 copy ( SELECT CUST_NO ,TO_CHAR(REG_DTTM,'YYYY-MM-DD HH24:MI:SS') FROM ZZZ) to '/archive/dw.dat' with delimiter '|' cf. export copy (select * from from sql_trace where snap_dt >= '$DATE2' and snap_dt < '$DATE3' order by snap_dt) to '/Postgres/9.2/oradba/sql_move.csv' CSV QUOTE '"'

stdin을 사용한 COPY FROM

COPY test (pk, id, name) FROM stdin; 1 1 ee 2 1 ee 3 1 ee 4 1 ee 5 1 ee 6 1 ee \.

레퍼런스

https://www.postgresql.org/docs/9.1/static/sql-copy.html