How to import and export csv files with Postgresql

Postgresql is growing in the enterprise and importing and exporting data or csv files from a legacy database is a fundamental task that developers and database administrators need to performed on a regular basis. The PGAdmin tool that is used to graphically administer postgresql databases does not have this functionality build in. Postgresql, however does make provision for this. There are two ways to copy csv files to and from postgresql databases. You can use the SQL "copy" statement or you can use the psql "\copy" command.

Importing csv files into postgresql

The format of the SQL copy statement is reproduced from the postgresql manual below:

COPY [ BINARY ] table [ WITH OIDS ]
FROM { 'filename' | stdin }
[ [ USING ] DELIMITERS 'delimiter' ]
[ WITH NULL AS 'null_string' ]


When we get a spreadsheet file, such as Excel or Open Office Spreadsheet from a client we first convert it to a cvs file by doing a "save as" csv. Next we prepare a staging table in the postgres target database which basically has the same number of columns, of type varchar, as there are fields in the csv file. Then we import the csv by running the sql statement

copy product from '/home/mark/products_and_rates.csv' using delimiters ','

You will need to have insert privileges on the table you are inserting into. The command will stop on the first error and roll back. Once in the staging tables you can use normal sql to insert and manipulate the data into the final target tables.

Exporting data in csv format from postgresql database

To export data to csv you can also use the SQL copy command. The syntax is given below:

COPY [ BINARY ] table [ WITH OIDS ]
TO { 'filename' | stdout }
[ [ USING ] DELIMITERS 'delimiter' ]
[ WITH NULL AS 'null_string' ]


You will probably run into file permission issues though. This is because the sql command runs under the same user as the postgresql service which usually does not have write permission to the filesystem. A way around this is to use the psql client "\copy" command.

This command has a similar syntax to the sql "copy" statement but runs under the same userid as the psql client which usually has write permissions to the file system. You can export data to csv by running the command as follows, from within psql:

\copy product to '/home/mark/Desktop/product.csv' delimiters ','

The "\copy" command can also be used to import csv data, same as the sql "copy" statement.

 

 

Comments

It seems the syntax has changed for postgresql copy.

Please read documentation
http://www.postgresql.org/docs/8.2/static/sql-copy.html

Thanks to northcustoms at ya.ru

a simple way to export a psql table in to CSV file using pgadmin:

1. write a select query for the table: select * from employee;
2. Run with 'Execute query, write result to file' option from the tool bar
3. Give column seperater as ','
4. check the column names checkbox
5. give target filename with csv extension and press OK