PostgreSQL for Data Architects

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=# \! ...