Write Data Between Table And File in Postgres

Write Data between Table and File

Postgres offers COPY commands for writing table data into text files (ascii or binary) and also write back from these files to postgres table.

The COPY TO command is used to copy the content of a table to a standard text file. If you specify column name of a table, while using COPY TO, the specified fields only will be written to a file. COPY FROM command is used to copy the content of a file to a database table.

Copy Data from Table to File

The following command is used to copy data from table to file.

Syntax
COPY <tablename> [(<column list,...>)] TO {'<filename>'}

where

tablename - Name of the table that we are going to copy
Column List - Column Names of the given table
File Name - Specify the file to which data should be written to

Example

Copy All fields to file

 postgres=#COPY student TO '/tmp/stud1.txt';

The above command will copy all the fields from student table to stud.txt file.

Copy Specified Field Only

postgres=#COPY student(rollno,total,avg) TO '/tmp/stud2.txt';

The above command will copy only specified fields from the student table to the stud.txt file and the file is saved in /tmp/ directory.
OutPut  
Sample Files


Copy Data from File to Table

The following command is used to copy data from File to Table.

Syntax

COPY <tablename> [(<column list,...>)] FROM {'<filename>'}

where

tablename -Name of the table that we are going to copy
Column List - Column Names of the given table
File Name - Specify the file name from which data will be copied to table.

Example
Copy All fields to Table

 postgres=#COPY student FROM '/tmp/stud1.txt';

The above command will copy the file named stud1.txt data to the student table.

Copy Only Speicified fields to Table

 postgres=#COPY student(name,total,avg) FROM '/tmp/stud2.txt'; 

The above command will copy data from the given file to the specified fields in student table.

Output

See More in http://www.postgresql.org/docs/8.1/static/sql-copy.html

Advertisements