COPY File Format

COPY...TO can export data to be loaded into other applications, and COPY...FROM can import data from other applications. If you are constructing a file for use with the COPY command or are reading a COPY file in another application, it is important to understand COPY's file format.

Figure  shows the contents of the COPY file from Figure .  

        test=> \q
        $ cat /tmp/copytest.out
        1       23.99   fresh spring water      t
        2       55.23   bottled soda    t
         
        $ sed 's/       /<TAB>/g' /tmp/copytest.out  # the gap between /  / is a TAB
        1<TAB>23.99<TAB>fresh spring water<TAB>t
        2<TAB>55.23<TAB>bottled soda<TAB>t  

First, \q exits psql to an operating system prompt. Then, the Unix cat^20.1 command displays the file /tmp/copytest.out. This file contains one line for every row in the table. Columns in the file are separated by tabs. These tabs are called delimiters  because they delimit (that is, separate) columns.

Tabs  are difficult to see because they look like multiple spaces. The next command processes the file using sed ^20.2 to display tabs as &LT;TAB>. This option clearly shows the tabs in the file, which differ from spaces.

The columns in Figure  do not line up as they do in psql, because they are of different lengths. The value of textcol in the first line is longer than the value in the second line. The lack of alignment is expected because the COPY file is designed for easy processing, with one tab between each column. It is not designed for display purposes.