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...