Sometime during your career as a developer, you’re going to want to save a SQL query’s result as a CSV. Most of the time you will be happy with the default table format that databases show results in. When you’re working with small amounts of data or if you’re just looking at it yourself, this is fine. But if you want to browse through large amounts of data or if your non-developer boss starts wanting to see the data, saving it to a CSV file is a great choice.
Luckily, Postgres can output its query results to CSV format. In this post, I’m going to show you how to save CSV files when the database is on your local machine or one that you can SSH to. I’m also going to show you how to do it for databases on a remote server that you can’t SSH into. Having a database run on its own server separate from your application is a common security and scalability practice. Sometimes you might not have SSH access to that server but still want to get a CSV from it.
Disclaimer
I don’t recommend using this method—or any method really—to export sensitive data. Leave that data in your database.
The Two Copy Commands
There are two versions of the copy command: COPY and \copy. COPY is a SQL instruction you run from within Postgres. \copy is a psql command that you run from the command line. \copy uses the same COPY instruction under the hood, but streams the file contents through stdin or stdout. The difference between the two has to do with file access. If your Postgres instance has access and permission to where you want to save the file, you can use COPY. If it doesn’t, you need to run the query through psql using \copy from a terminal that does have access to where you want to save the file.
For both examples below, I’m going to use this query which returns this set of data:
1
2
3
4
5
6
7
8
9
select * from users;
id | first_name | last_name | email
----+------------+------------+--------------------
1 | Frodo | Baggins | frodo@example.com
2 | Samwise | Gamgee | sam@example.com
3 | Meriadoc | Brandybuck | merry@example.com
4 | Peregrin | Took | pippin@example.com
(4 rows)
Using COPY
If your database is running on a machine that has access to where you want to save the file, using COPY is the easiest method. Try this method first if your database is running on your computer or if you have SSH access to the server it’s running on.
Here is the basic syntax:
1
2
3
4
COPY table_name_or_query
TO 'filename'
WITH (format csv, header true)
;
You can export a whole table or a query. If you’re exporting a whole table, you don’t need parenthesis around the table name. If you’re exporting a query, put it in parenthesis. I’ll use a query because I think that’s the most common use case.
Example
I’ll start from the command line so you can see the whole process. If you aren’t very familiar with the command line or haven’t set yours up yet, check out my article How to Make Your Terminal Amazing.
First I’ll see what files are in my current folder and then enter the database:
1
2
3
4
5
6
7
8
9
~/code/examples/
❯ ls
~/code/examples/
❯ psql example_db
psql (12.3)
Type "help" for help.
example_db=#
There are no files in the example folder yet. Once inside the database, example_db, we’ll run our query:
1
2
3
4
5
example_db=# COPY
example_db-# (select * from users)
example_db-# TO '/Users/tracehelms/code/examples/users.csv'
example_db-# WITH (format csv, header true);
COPY 4
And now we’ll exit the database and make sure we have our CSV file:
1
2
3
4
5
example_db=# \q
~/code/examples/
❯ ls
users.csv
Great, there it is! And here are the contents of that file:
1
2
3
4
5
6
7
8
~/code/examples/
❯ cat users.csv
id,first_name,last_name,email
1,Frodo,Baggins,frodo@example.com
2,Samwise,Gamgee,sam@example.com
3,Meriadoc,Brandybuck,merry@example.com
4,Peregrin,Took,pippin@example.com
If the CSV file is where you have access to it, you’re done. But if it’s on a remote server that you had to SSH into first, I have an article that explains how to get it onto your local computer: Using SCP To Download And Upload Files Through SSH.
Using \copy
If your database doesn’t have access to where you want to store the file, you can use \copy with psql. Use this method if your database is running on a server and you only have the database URL, not SSH access. Also, if you have any quotes (") in your query, like SELECT first_name as "First Name", you will need to escape them like this: SELECT first_name as \"First Name\". You might have quotes in your query if you’re renaming the column names to be fancy.
Here is the basic syntax:
1
2
3
4
"\copy
table_name_or_query
to 'filename'
with (format csv, header true);"
Keep in mind that if it’s just a table name, don’t surround it in parenthesis. If it’s a query, put parenthesis around it. And you run this by using the psql -c (command) flag from your terminal:
1
2
psql database_name_or_url -c "\copy table_name_or_query to 'filename'
with (format csv, header true);"
For database_name_or_url, you can simply use the database name if you’re already on the machine running the database. More likely though, you’ll want to run this using a database’s URL. The syntax for Postgres URL’s is usually postgresql://user:password@url.com/databasename. The Postgres documentation goes into great detail about it too. If you’re having trouble finding this URL, check your application configuration (like Rails’ or Phoenix’s database configuration) to see what value it’s using and try that.
Example
Let’s use our same query from the previous example. We can simply place that query—with any quotes escaped—inside of the \copy syntax. I’ll start on the command line again by seeing what’s in our current folder.
1
2
3
~/code/examples/
❯ ls
And then we’ll run the psql command:
1
2
3
4
5
6
~/code/examples/
❯ psql postgresql://trace:secret@myapp.us-east-1.amazonaws.com/example_db -c
"\copy (select * from users)
to '/Users/tracehelms/code/examples/users.csv'
with (format csv, header true);"
COPY 4
We don’t have to exit back to the command line because we’re telling psql to run the query for us. And now let’s check if the file is there:
1
2
3
~/code/examples/
❯ ls
users.csv
And there it is! If you ran this command from a remote server, you can use SCP and SSH to get it on your local computer. My article, Using SCP To Download And Upload Files Through SSH, shows you how to do that.
Wrap Up
With either method, you should now have a CSV file with the contents of your query. Figuring out when to use COPY vs. \copy is tricky at first, but just remember that it depends on file access. Use COPY when your database can access where you want to save the file and use \copy when it can’t.
I have found this to be immensely helpful in the situations when I need it. It’s the best way to give someone else the results of a query so they can read it with Excel. The next time a co-worker asks for a set of information from your database, I hope you think of this. Just be careful that you’re not sharing sensitive data this way.