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

About Ravishankar L
Funny, smart, relax, love , smile, mercy, helpful

2 Responses to Write Data Between Table And File in Postgres

  1. This is very attention-grabbing, You’re an excessively professional blogger. I have joined your rss feed and stay up for in search of extra of your magnificent post. Also, I have shared your web site in my social networks

  2. ashok says:

    Is there any way to read and write image,doc,xls file from c:\ to postgre table? and vice versa?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: