COPY

Now, let's look at the easiest way of moving data from PostgreSQL tables to files or the other way around: the COPY command. We will see the table to file options first:

postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# CREATE TABLE myt (id int, nm varchar(20));
CREATE TABLE
test=# INSERT INTO myt VALUES(1,'First record');
INSERT 0 1
test=# INSERT INTO myt VALUES(2,'Second record');
INSERT 0 1
test=# COPY myt TO '/tmp/file.csv';
COPY 2
test=# \! cat /tmp/file.csv
1 First record
2 Second record

The simplest use of the command is COPY TABLE TO FILE. Instead of using the table name, we can use a SELECT command:

test=# \! rm /tmp/file.csv
test=# COPY ( select * from myt ) to '/tmp/file.csv';
COPY 2
test=# \! head /tmp/file.csv
1  First record
2  Second record

We named the file with the .csv extension, but the file generated is not really separated by comma. It used text as the default format with tab as the column separator. For the .csv output, we have...