설문조사
PostgreSQL/PPAS 관련 듣고 싶은 교육은
총 게시물 11건, 최근 0 건
   

CSV 화일로 테이블 export/import 하기

글쓴이 : PostgresDBA 날짜 : 2012-12-15 (토) 14:50 조회 : 27489
테이블 데이터를 텍스트화일로 export 해보겠습니다.

[postgres@olmaster:~/oradba/csv]$ alias scott
alias scott='psql -d scottdb -U scott -W'

[postgres@olmaster:~/oradba/csv]$ scott
Password for user scott: 
Null display is "NULL".
Timing is on.
Pager is always used.
psql (9.2.1)
Type "help" for help.

scott@[local]:5432 scottdb#SQL> \copy emp to '~/oradba/csv/emp1.csv';
Time: 1.570 ms
scott@[local]:5432 scottdb#SQL> \! cat ~/oradba/csv/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
7698    BLAKE   MANAGER 7839    1981-05-01      2850    \N      30
7782    CLARK   MANAGER 7839    1981-06-09      2450    \N      10
7788    SCOTT   ANALYST 7566    1982-12-09      3000    \N      20
7839    KING    PRESIDENT       \N      1981-11-17      5000    \N      10
7844    TURNER  SALESMAN        7698    1981-09-08      1500    0       30
7876    ADAMS   CLERK   7788    1983-01-12      1100    \N      20
7900    JAMES   CLERK   7698    1981-12-03      950     \N      30
7902    FORD    ANALYST 7566    1981-12-03      3000    \N      20
7934    MILLER  CLERK   7782    1982-01-23      1300    \N      10
scott@[local]:5432 scottdb#SQL> \copy emp(empno,ename) to '~/oradba/csv/emp2.csv';
Time: 0.480 ms
scott@[local]:5432 scottdb#SQL> \! cat ~/oradba/csv/emp2.csv
7369    SMITH
7499    ALLEN
7521    WARD
7566    JONES
7654    MARTIN
7698    BLAKE
7782    CLARK
7788    SCOTT
7839    KING
7844    TURNER
7876    ADAMS
7900    JAMES
7902    FORD
7934    MILLER
scott@[local]:5432 scottdb#SQL> \copy emp(empno,ename) to '~/oradba/csv/emp3.csv' with delimiter ',' csv header
Time: 0.357 ms
scott@[local]:5432 scottdb#SQL> \! cat ~/oradba/csv/emp3.csv
empno,ename
7369,SMITH
7499,ALLEN
7521,WARD
7566,JONES
7654,MARTIN
7698,BLAKE
7782,CLARK
7788,SCOTT
7839,KING
7844,TURNER
7876,ADAMS
7900,JAMES
7902,FORD
7934,MILLER
scott@[local]:5432 scottdb#SQL> 

이제 새 테이블을 생성후에, emp3.csv 화일을  import 해보겠습니다.

[postgres@olmaster:~/oradba/csv]$ cat emp3.csv 
empno,ename
7369,SMITH
7499,ALLEN
7521,WARD
7566,JONES
7654,MARTIN
7698,BLAKE
7782,CLARK
7788,SCOTT
7839,KING
7844,TURNER
7876,ADAMS
7900,JAMES
7902,FORD
7934,MILLER
[postgres@olmaster:~/oradba/csv]$ scott
Password for user scott: 
Null display is "NULL".
Timing is on.
Pager is always used.
psql (9.2.1)
Type "help" for help.

scott@[local]:5432 scottdb#SQL> create table customer(no integer, name varchar(32));
CREATE TABLE
Time: 101.081 ms
scott@[local]:5432 scottdb#SQL> \copy customer(no,name) from '~/oradba/csv/emp3.csv' with delimiter ',' csv header
Time: 1.632 ms
scott@[local]:5432 scottdb#SQL> select * from customer;
  no  |  name  
------+--------
 7369 | SMITH
 7499 | ALLEN
 7521 | WARD
 7566 | JONES
 7654 | MARTIN
 7698 | BLAKE
 7782 | CLARK
 7788 | SCOTT
 7839 | KING
 7844 | TURNER
 7876 | ADAMS
 7900 | JAMES
 7902 | FORD
 7934 | MILLER
(14 rows)

Time: 0.481 ms
scott@[local]:5432 scottdb#SQL> 

오라클에 비하면, PostgreSQL 의 이런 기능은 너무 좋네요.


-----------------2013.07.12 추가 ------------------

필드값 내에 "," 가 들어가 있다면 다음과 같이 하면 됩니다.
[enterprisscottdb@dpcpdb03 oradba]$ cat a.csv
1,2,"super,man","spider","man"

[enterprisscottdb@dpcpdb03 oradba]$ psql
psql (9.2.4.8)
Type "help" for help.

scottdb=# create table ccc(a varchar(10), b varchar(10), c varchar(10), d varchar(10), e varchar(10));
CREATE TABLE
scottdb=# \copy ccc(a,b,c,d,e) from '~/oradba/a.csv' with csv  quote '"'
scottdb=# select * from ccc;
 a | b |     c     |   d    |  e  
---+---+-----------+--------+-----
 1 | 2 | super,man | spider | man
(1 row)

scottdb=# 

cf. 컬럼변환이 필요하면
 \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 '"'

다욱 2013-04-29 (월) 18:12
혹시 이렇게 바로 bulk insert하는 것 말고, table select하듯이 csv파일을 읽어오는 기능도 있나요?
댓글주소
PostgresDBA 2013-04-30 (화) 12:00
오라클의 external table 같은 기능을 말씀하시는거군요.
아쉽게도, PostgreSQL 에는 built-in 되어 있는 그런기능은 없습니다.
댓글주소
PostgresDBA 2013-05-16 (목) 10:33
정정:
PostgreSQL 에서도 쉽게 구현할수 있네요.

SQL 카테고리란에 Foreign Data Wrappers(FDW)에 관한 글을 올렸습니다.
CSV 화일을 테이블 SELECT 처럼 읽어 들이는 기능에 관한 글입니다.
댓글주소
PostgresDBA 2016-12-22 (목) 18:33
널과 empty string('') export
------------------------------------------------------------------------------

SQL> create table x(a text, b text, c text, d int);
CREATE TABLE

SQL> drop table x;
DROP TABLE

SQL> create table x(a int, b text, c text, d int);
CREATE TABLE

SQL> insert into x values(1,null,'',2);
INSERT 0 1

SQL> \copy x to './a.txt' with csv
COPY 1

SQL> \! cat a.txt
1,,"",2

SQL> \copy x to './a.txt' with csv force quote *
COPY 1

SQL> \! cat a.txt
"1",,"","2"

SQL>
댓글주소
PostgresDBA 2017-03-07 (화) 13:43
탭구분자 형태의 csv 화일 읽어들이기
\copy t_crm from '/ppas/oradba/20170306/all.txt' with (format csv, delimiter E'\t')
댓글주소
PostgresDBA 2017-10-20 (금) 10:17
copy 하면서 실시간 압축하기 예제
------------------------------------------------
psql -U dba -d dtest -c "copy (SELECT USE_DT , USE_HHMI_SEC  ITEM_CD FROM my.bigtable_pt_2011) to stdout with csv  quote '\"'" | gzip > bigtable_2011.csv.gz
psql -U dba -d dtest -c "copy (SELECT USE_DT , USE_HHMI_SEC  ITEM_CD FROM my.bigtable_pt_2012) to stdout with csv  quote '\"'" | gzip > bigtable_2012.csv.gz
psql -U dba -d dtest -c "copy (SELECT USE_DT , USE_HHMI_SEC  ITEM_CD FROM my.bigtable_pt_2013) to stdout with csv  quote '\"'" | gzip > bigtable_2013.csv.gz
psql -U dba -d dtest -c "copy (SELECT USE_DT , USE_HHMI_SEC  ITEM_CD FROM my.bigtable_pt_2014) to stdout with csv  quote '\"'" | gzip > bigtable_2014.csv.gz
psql -U dba -d dtest -c "copy (SELECT USE_DT , USE_HHMI_SEC  ITEM_CD FROM my.bigtable_pt_2015) to stdout with csv  quote '\"'" | gzip > bigtable_2015.csv.gz
psql -U dba -d dtest -c "copy (SELECT USE_DT , USE_HHMI_SEC  ITEM_CD FROM my.bigtable_pt_2016) to stdout with csv  quote '\"'" | gzip > bigtable_2016.csv.gz
psql -U dba -d dtest -c "copy (SELECT USE_DT , USE_HHMI_SEC  ITEM_CD FROM my.bigtable_pt_2017 WHERE USE_DT < '20171001') to stdout with csv  quote '\"'" | gzip > bigtable_2017.csv.gz
댓글주소
     
     
모모와도도 2019-01-07 (월) 11:46
특정 파티션 테이블에 대해서 작업 시 (조회쿼리만 들어오는 상태) 서비스 영향도가 클 까요?
postgresql 의 동일한 부모의 다른 파티션 테이블 작업에 영향을 준다던지... 검증을 해보고 싶지만
상황이 여의치 않아 경험 여쭤봅니다.
댓글주소
   

postgresdba.com
| 개인정보취급방침 | 책임의한계와 법적고지 | 이메일무단수집거부
Copyright ⓒ PostgresDBA.COM All rights reserved.
E-MAIL: root@postgresdba.com