[PGS9.2] How to use Import and Export with COPY

/********************************************************************************************
-- Title : [SQL9.2] How to use Import and Export with COPY

-- Reference : dbrang.tistory.com
-- Key word : postgresql import export copy 임포트 익스포트 카피
********************************************************************************************/

----------------

-- Set Test Env.

----------------

-- Initiate 

drop table ttt;

drop sequence seq_ttt_a;

-- Create Sequence

create sequence seq_ttt_a

    start with 1

    increment by 1

    no minvalue

    no maxvalue

    cache 1;

-- Create Table 

create table ttt

( a int default nextval('seq_ttt_a'::regclass) not null /* need DEFAULT */

, b varchar(10)

, c text

);

create table ttt_imp

( a int, b varchar(10), c text);   /* Imported Table */

insert into ttt (b, c)

values ('aaa', 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa')

     , ('bbb', 'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb')

     , ('ccc', 'ccccccccccccccccccccccccccccc')

     , ('ddd', 'dddddddddddddddddddddddddddddddddddddddddddd')

     , ('eee', 'eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee')

     , ('fff', 'fffffffffffffffffffffffffffffffffffff')

     , ('ggg', 'gggggggggggggggggggggg')

     , ('hhh', 'hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh')

     , ('iii', 'iiiiiiiiiiiiiiiiiiiiiiiiiii')

     , ('jjj', 'jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj')

     , ('kkk', 'kkkkkkkkkkkkkkk')

     , ('lll', 'lllllllllllllllllllllllllllllllllllllllllll')

     , ('mmm', 'mmmmmmmmmmmmm')

     , ('nnn', 'nnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn')

     , ('ooo', 'oooooooooooooooooooooooooooooooooooo')

     , ('ppp', 'ppppppppppppppppppppppppp')

     , ('qqq', 'qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq')

     , ('rrr', 'rrrrrrrrrrrrrrrrrrrrrrrrrrr');

-- Show Data

select * from ttt;

----------------------

-- Export Data to .csv

----------------------

-- using psql -c

$ psql tttdb -c "select a, b, c from ttt;" -A -F"," -t > /home/postgres/test.csv  /* -t : header */

$ psql tttdb -c "select a, b, c from ttt;" -A -F"," > /home/postgres/test2.csv

-- using copy

=# \c tttdb;

=# copy ttt to '/home/postgres/test3.csv' delimiters ',';

-- using copy with STDOUT

=# COPY ttt TO STDOUT (DELIMITER '|');   /* displayed on the screen */

-- using copy with SELECT clause

=# copy (select a,b from ttt) to '/home/postgres/test4.csv' with csv;

=# copy (select a,b from ttt where b = 'aaa' ) to '/home/postgres/test5.csv' with csv;

-- using copy with CSV HEADER

=# copy ttt(a,b,c) to '/home/postgres/test5.csv' with csv header;

-- using psql \o

=# \o '/home/postgres/test9.csv'

=# select * from ttt;

=# \q

------------------------

-- Import Data from .csv

------------------------

-- using copy

=# COPY ttt_imp (a,b,c) FROM '/home/postgres/test3.csv' with delimiter ',';

-- using psql -c

$ psql tttdb -c "copy ttt_imp from '/home/postgres/test3.csv' with delimiter ',';"

-- using copy NULL     /* didn't work */

=# copy ttt_imp from '/home/postgres/test3.csv' with delimiter ',' NULL as 'null' csv;

-- using copy with \t  

=# copy ttt_imp from '/home/postgres/test_tab.csv' with delimiter E'\t' NULL as 'null' csv;

-- ERROR:  COPY delimiter must be a single one-byte character
    o Have to attach a prefix 'E' to delimiter char.
    o =# copy ttt_imp from '/home/postgres/test_tab.csv' with delimiter E'\t' csv;