Using COPY

COPY...TO allows you to copy the contents of a table to a file. The file can later be read using COPY...FROM.

Figure  shows the creation of a table with columns of various types.  

        test=> CREATE TABLE copytest (
        test(>                         intcol  INTEGER,
        test(>                         numcol  NUMERIC(16,2),
        test(>                         textcol TEXT,
        test(>                         boolcol BOOLEAN
        test(> );
        CREATE
        test=> INSERT INTO copytest
        test-> VALUES (1, 23.99, 'fresh spring water', 't');
        INSERT 174656 1
        test=> INSERT INTO copytest
        test-> VALUES (2, 55.23, 'bottled soda', 't');
        INSERT 174657 1
        test=> SELECT * FROM copytest;
         intcol | numcol |      textcol       | boolcol 
        --------+--------+--------------------+---------
              1 |  23.99 | fresh spring water | t
              2 |  55.23 | bottled soda       | t
        (2 rows) 
         
        test=> COPY copytest TO '/tmp/copytest.out';
        COPY
        test=> DELETE FROM copytest;
        DELETE 2
        test=> COPY copytest FROM '/tmp/copytest.out';
        COPY
        test=> SELECT * FROM copytest;
         intcol | numcol |      textcol       | boolcol 
        --------+--------+--------------------+---------
              1 |  23.99 | fresh spring water | t
              2 |  55.23 | bottled soda       | t
        (2 rows)  

Two rows are then inserted into copytest. SELECT shows the contents of the table, and COPY...TO writes the table to the file /tmp/copytest.out. The rows are then deleted. Finally, COPY...FROM reloads the table, as shown by the last SELECT.

COPY provides a quick way to load and unload tables. It is used for database backup  (see Section ). The following sections cover various COPY features that are important when reading or writing COPY files in other applications.