Tuesday, May 10, 2011

How To Export PostgreSQL Table Data To CSV File

When exporting data from a previous database, most people want to export to CSV format. Here I show you how to do just that. This isn't a fancy or complicated guide to exporting with PostgreSQL, just a quickie for how to export CSV data from a table in PostgreSQL.

Step 1 - Login To PostgreSQL Command Line
Open you terminal (be it Gnome Terminal or Konsole....or xterm for you weirdos). Log into the PostgreSQL command line using the following command:

psql -u <username> -h <hostname> -d <database> -p
Password: <password>


Replace <username>, <hostname>, and <database> with the correct information for your setup. If it requests a password, enter the password for that user. If you just want to access your local user database, simple use "psql" without any parameters and it should work fine.

This command will bring you to a prompt with the current database + "=#". This is how you know you're logged in and ready to go to step 2.

Step 2 - Export To CSV File
The following command can be used to export to a CSV file. Try it out:

copy (select * from mytable) to '/path/to/file.csv' with csv header;

The select query can be replaced with ANYTHING you want. The above example just gets everything from the table. You can replace the query with one that will grab for example the last 100 rows or only rows with the name "Foobar" or something. Your choice!

Replace the '/path/to/file.csv' with the full path to the file you want to export to. Also, above you can remove "with csv header" if you don't want headers in your generated CSV file. I could explain the difference but if you really want to know just run the command with and without the "with csv header" part and you'll see.

Step 3 - In Case You're A Blazing Idiot
Here are a few things to look at if the above doesn't work. Let me know if something doesn't work that's not in this list.
  • Make sure the folder you are pointing to exists. Otherwise the copy command will give you an error saying that the file doesn't exist.
  • You don't need to use the *.csv extension for the CSV file, but you probably should since conventions are good for you! Oh and also so you can know what's in the file later, which helps in the long run.
  • Make sure the file path is inside single quotes only! I tried using no quotes and double quotes, and they don't work.

Step 4 - Knock Yourself Out
Try it out with your own tables! I think even if you don't need to export data into CSV format, it's useful to at least keep that around for when you do. How about bookmarking this page?

2 comments:

  1. Really Good Post!!

    Looking to add an iPod input or Bluetooth connectivity to your stereo car stereo Audio stereo? We have many integration upgrades for your pre-existing stereo system.

    ReplyDelete
  2. nice! I have found another article to import .csv into postgres table in a simple way visit http://www.etechpulse.com/2013/06/database-how-to-import-data-from-csv-in.html

    ReplyDelete