What is the easiest way to save PL/pgSQL output from a PostgreSQL database to a CSV file?
I'm using PostgreSQL 8.4 with pgAdmin III and PSQL plugin where I run queries from.
This question is related to
sql
postgresql
csv
postgresql-copy
I tried several things but few of them were able to give me the desired CSV with header details.
Here is what worked for me.
psql -d dbame -U username \
-c "COPY ( SELECT * FROM TABLE ) TO STDOUT WITH CSV HEADER " > \
OUTPUT_CSV_FILE.csv
New version - psql 12 - will support --csv
.
--csv
Switches to CSV (Comma-Separated Values) output mode. This is equivalent to \pset format csv.
csv_fieldsep
Specifies the field separator to be used in CSV output format. If the separator character appears in a field's value, that field is output within double quotes, following standard CSV rules. The default is a comma.
Usage:
psql -c "SELECT * FROM pg_catalog.pg_tables" --csv postgres
psql -c "SELECT * FROM pg_catalog.pg_tables" --csv -P csv_fieldsep='^' postgres
psql -c "SELECT * FROM pg_catalog.pg_tables" --csv postgres > output.csv
I'm working on AWS Redshift, which does not support the COPY TO
feature.
My BI tool supports tab-delimited CSVs though, so I used the following:
psql -h dblocation -p port -U user -d dbname -F $'\t' --no-align -c "SELECT * FROM TABLE" > outfile.csv
If you're interested in all the columns of a particular table along with headers, you can use
COPY table TO '/some_destdir/mycsv.csv' WITH CSV HEADER;
This is a tiny bit simpler than
COPY (SELECT * FROM table) TO '/some_destdir/mycsv.csv' WITH CSV HEADER;
which, to the best of my knowledge, are equivalent.
I had to use the \COPY because I received the error message:
ERROR: could not open file "/filepath/places.csv" for writing: Permission denied
So I used:
\Copy (Select address, zip From manjadata) To '/filepath/places.csv' With CSV;
and it is functioning
JackDB, a database client in your web browser, makes this really easy. Especially if you're on Heroku.
It lets you connect to remote databases and run SQL queries on them.
Source
(source: jackdb.com)
Once your DB is connected, you can run a query and export to CSV or TXT (see bottom right).
Note: I'm in no way affiliated with JackDB. I currently use their free services and think it's a great product.
Per the request of @skeller88, I am reposting my comment as an answer so that it doesn't get lost by people who don't read every response...
The problem with DataGrip is that it puts a grip on your wallet. It is not free. Try the community edition of DBeaver at dbeaver.io. It is a FOSS multi-platform database tool for SQL programmers, DBAs and analysts that supports all popular databases: MySQL, PostgreSQL, SQLite, Oracle, DB2, SQL Server, Sybase, MS Access, Teradata, Firebird, Hive, Presto, etc.
DBeaver Community Edition makes it trivial to connect to a database, issue queries to retrieve data, and then download the result set to save it to CSV, JSON, SQL, or other common data formats. It's a viable FOSS competitor to TOAD for Postgres, TOAD for SQL Server, or Toad for Oracle.
I have no affiliation with DBeaver. I love the price and functionality, but I wish they would open up the DBeaver/Eclipse application more and made it easy to add analytics widgets to DBeaver / Eclipse, rather than requiring users to pay for the annual subscription to create graphs and charts directly within the application. My Java coding skills are rusty and I don't feel like taking weeks to relearn how to build Eclipse widgets, only to find that DBeaver has disabled the ability to add third-party widgets to the DBeaver Community Edition.
Do DBeaver users have insight as to the steps to create analytics widgets to add into the Community Edition of DBeaver?
There are several solutions:
psql
commandpsql -d dbname -t -A -F"," -c "select * from users" > output.csv
This has the big advantage that you can using it via SSH, like ssh postgres@host command
- enabling you to get
copy
commandCOPY (SELECT * from users) To '/tmp/output.csv' With CSV;
>psql dbname
psql>\f ','
psql>\a
psql>\o '/tmp/output.csv'
psql>SELECT * from users;
psql>\q
All of them can be used in scripts, but I prefer #1.
To Download CSV file with column names as HEADER use this command:
Copy (Select * From tableName) To '/tmp/fileName.csv' With CSV HEADER;
In pgAdmin III there is an option to export to file from the query window. In the main menu it's Query -> Execute to file or there's a button that does the same thing (it's a green triangle with a blue floppy disk as opposed to the plain green triangle which just runs the query). If you're not running the query from the query window then I'd do what IMSoP suggested and use the copy command.
If you have longer query and you like to use psql then put your query to a file and use the following command:
psql -d my_db_name -t -A -F";" -f input-file.sql -o output-file.csv
import json
cursor = conn.cursor()
qry = """ SELECT details FROM test_csvfile """
cursor.execute(qry)
rows = cursor.fetchall()
value = json.dumps(rows)
with open("/home/asha/Desktop/Income_output.json","w+") as f:
f.write(value)
print 'Saved to File Successfully'
This information isn't really well represented. As this is the second time I've needed to derive this, I'll put this here to remind myself if nothing else.
Really the best way to do this (get CSV out of postgres) is to use the COPY ... TO STDOUT
command. Though you don't want to do it the way shown in the answers here. The correct way to use the command is:
COPY (select id, name from groups) TO STDOUT WITH CSV HEADER
It's great for use over ssh:
$ ssh psqlserver.example.com 'psql -d mydb "COPY (select id, name from groups) TO STDOUT WITH CSV HEADER"' > groups.csv
It's great for use inside docker over ssh:
$ ssh pgserver.example.com 'docker exec -tu postgres postgres psql -d mydb -c "COPY groups TO STDOUT WITH CSV HEADER"' > groups.csv
It's even great on the local machine:
$ psql -d mydb -c 'COPY groups TO STDOUT WITH CSV HEADER' > groups.csv
Or inside docker on the local machine?:
docker exec -tu postgres postgres psql -d mydb -c 'COPY groups TO STDOUT WITH CSV HEADER' > groups.csv
Or on a kubernetes cluster, in docker, over HTTPS??:
kubectl exec -t postgres-2592991581-ws2td 'psql -d mydb -c "COPY groups TO STDOUT WITH CSV HEADER"' > groups.csv
So versatile, much commas!
Yes I did, here are my notes:
Using /copy
effectively executes file operations on whatever system the psql
command is running on, as the user who is executing it1. If you connect to a remote server, it's simple to copy data files on the system executing psql
to/from the remote server.
COPY
executes file operations on the server as the backend process user account (default postgres
), file paths and permissions are checked and applied accordingly. If using TO STDOUT
then file permissions checks are bypassed.
Both of these options require subsequent file movement if psql
is not executing on the system where you want the resultant CSV to ultimately reside. This is the most likely case, in my experience, when you mostly work with remote servers.
It is more complex to configure something like a TCP/IP tunnel over ssh to a remote system for simple CSV output, but for other output formats (binary) it may be better to /copy
over a tunneled connection, executing a local psql
. In a similar vein, for large imports, moving the source file to the server and using COPY
is probably the highest-performance option.
With psql parameters you can format the output like CSV but there are downsides like having to remember to disable the pager and not getting headers:
$ psql -P pager=off -d mydb -t -A -F',' -c 'select * from groups;'
2,Technician,Test 2,,,t,,0,,
3,Truck,1,2017-10-02,,t,,0,,
4,Truck,2,2017-10-02,,t,,0,,
No, I just want to get CSV out of my server without compiling and/or installing a tool.
psql
can do this for you:
edd@ron:~$ psql -d beancounter -t -A -F"," \
-c "select date, symbol, day_close " \
"from stockprices where symbol like 'I%' " \
"and date >= '2009-10-02'"
2009-10-02,IBM,119.02
2009-10-02,IEF,92.77
2009-10-02,IEV,37.05
2009-10-02,IJH,66.18
2009-10-02,IJR,50.33
2009-10-02,ILF,42.24
2009-10-02,INTC,18.97
2009-10-02,IP,21.39
edd@ron:~$
See man psql
for help on the options used here.
I've written a little tool called psql2csv
that encapsulates the COPY query TO STDOUT
pattern, resulting in proper CSV. It's interface is similar to psql
.
psql2csv [OPTIONS] < QUERY
psql2csv [OPTIONS] QUERY
The query is assumed to be the contents of STDIN, if present, or the last argument. All other arguments are forwarded to psql except for these:
-h, --help show help, then exit
--encoding=ENCODING use a different encoding than UTF8 (Excel likes LATIN1)
--no-header do not output a header
In terminal (while connected to the db) set output to the cvs file
1) Set field seperator to ','
:
\f ','
2) Set output format unaligned:
\a
3) Show only tuples:
\t
4) Set output:
\o '/tmp/yourOutputFile.csv'
5) Execute your query:
:select * from YOUR_TABLE
6) Output:
\o
You will then be able to find your csv file in this location:
cd /tmp
Copy it using the scp
command or edit using nano:
nano /tmp/yourOutputFile.csv
Source: Stackoverflow.com