Contents /
Previous /
Next
Import and Export Data: COPY
COPY allows loading and unloading of user tables to ASCII files.
It can be used for backup purposes or to
transfer data between PostgreSQL and other applications.
Using COPY:
<![CDATA[
COPY [ BINARY ] table [ WITH OIDS ]
FROM { 'filename' | stdin }
[ [USING] DELIMITERS 'delimiter' ]
[ WITH NULL AS 'null string' ]
]]>
Example:
<![CDATA[
SELECT * FROM stud_sel ;
id | name
----+------
1 | fred
3 | tom
4 | john
2 | lisa
(4 rows)
COPY stud_sel TO '/home/felix/tmp/stud.out' ;
]]>
The output file is in ASCII:
<![CDATA[
~/tmp> more stud.out
1 fred
3 tom
4 john
2 lisa
]]>
Now we DROP the stud_sel table and recover it from the stud.out file:
<![CDATA[
DELETE FROM stud_sel ;
SELECT * FROM stud_sel ;
id | name
----+------
(0 rows)
COPY stud_sel FROM '/home/felix/tmp/stud.out' ;
SELECT * FROM stud_sel ;
id | name
----+------
1 | fred
3 | tom
4 | john
2 | lisa
(4 rows)
]]>
You must use full path names with COPY.
Files are read and written by the postgres user,
who must have necessary permissions.
COPY writes only entire tables.
When you change the default tab "\t" column delimiter saving a
file, the same delimiter has to be specified for reloading it.
To change the default NULL representation use WITH NULL AS:
<![CDATA[
COPY stud_sel TO '/home/felix/tmp/stud.out' WITH NULL AS '?'
]]>
Copy from/to STDIO
COPY can also be used the standard input/output (stdin/stdout).
Using stdin COPY input can be supplied directly from your keyboard:
<![CDATA[
COPY stud_sel FROM stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 8 Thomas
>> 9 Daniel
>> \.
workshop=# SELECT * FROM stud_sel ;
id | name
----+--------
1 | fred
3 | tom
4 | john
2 | lisa
8 | Thomas
9 | Daniel
(6 rows)
]]>
The gaps typed data lines were generated by pressing the tab key
and finished with " \."
A COPY to stdout operation
displays the COPY output on your screen, which can be useful when
using psql in automated scripts.