Step 1 - Login To PostgreSQL Command LineOpen 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>
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 IdiotHere 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 OutTry 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?