You can easily change the default tab column delimiter. COPY's USING
DELIMITERS option allows you to set the column delimiter. In Figure ,
setting the delimiter to a pipe symbol () causes the output file to
use pipes to separate columns.
test=> COPY copytest TO '/tmp/copytest.out' USING DELIMITERS '|';
COPY
test=> \q
$ cat /tmp/copytest.out
1|23.99|fresh spring water|t
2|55.23|bottled soda|t
If a COPY file does not use the default tab column delimiter,
COPY...FROM must employ the proper USING DELIMITERS
option. As shown in Figure , if
a file uses pipes rather than tabs as column delimiters, COPY...FROM
must specify pipes as delimiters.
test=> DELETE FROM copytest;
DELETE 2
test=>
test=> COPY copytest FROM '/tmp/copytest.out';
ERROR: copy: line 1, pg_atoi: error in "1|23.99|fresh spring water|t": cannot parse "|23.99|fresh spring water|t"
test=>
test=> COPY copytest FROM '/tmp/copytest.out' USING DELIMITERS '|';
COPY
The first COPY...FROM fails because it cannot find a tab to separate the columns. The second COPY...FROM succeeds because the proper delimiter for the file was used.