Save PL pgSQL output from PostgreSQL to a CSV file

Question

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

User · Answer

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

User · Answer

Do you want the resulting file on the server  or on the client   Server side  If you want something easy to re-use or automate  you can use Postgresql s built in COPY command  e g   Copy  Select   From foo  To   tmp test csv  With CSV DELIMITER     HEADER    This approach runs entirely on the remote server - it can t write to your local PC  It also needs to be run as a Postgres  superuser   normally called  root   because Postgres can t stop it doing nasty things with that machine s local filesystem   That doesn t actually mean you have to be connected as a superuser  automating that would be a security risk of a different kind   because you can use the SECURITY DEFINER option to CREATE FUNCTION to make a function which runs as though you were a superuser    The crucial part is that your function is there to perform additional checks  not just by-pass the security - so you could write a function which exports the exact data you need  or you could write something which can accept various options as long as they meet a strict whitelist  You need to check two things    Which files should the user be allowed to read write on disk  This might be a particular directory  for instance  and the filename might have to have a suitable prefix or extension  Which tables should the user be able to read write in the database  This would normally be defined by GRANTs in the database  but the function is now running as a superuser  so tables which would normally be  out of bounds  will be fully accessible  You probably don   t want to let someone invoke your function and add rows on the end of your    users    table      I ve written a blog post expanding on this approach  including some examples of functions that export  or import  files and tables meeting strict conditions     Client side  The other approach is to do the file handling on the client side  i e  in your application or script  The Postgres server doesn t need to know what file you re copying to  it just spits out the data and the client puts it somewhere   The underlying syntax for this is the COPY TO STDOUT command  and graphical tools like pgAdmin will wrap it for you in a nice dialog   The psql command-line client has a special  meta-command  called  copy  which takes all the same options as the  real  COPY  but is run inside the client    copy  Select   From foo  To   tmp test csv  With CSV   Note that there is no terminating    because meta-commands are terminated by newline  unlike SQL commands   From the docs      Do not confuse COPY with the psql instruction  copy   copy invokes COPY FROM STDIN or COPY TO STDOUT  and then fetches stores the data in a file accessible to the psql client  Thus  file accessibility and access rights depend on the client rather than the server when  copy is used    Your application programming language may also have support for pushing or fetching the data  but you cannot generally use COPY FROM STDIN TO STDOUT within a standard SQL statement  because there is no way of connecting the input output stream  PHP s PostgreSQL handler  not PDO  includes very basic pg copy from and pg copy to functions which copy to from a PHP array  which may not be efficient for large data sets

User · Answer

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   lt  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

User · Answer

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    gt      OUTPUT CSV FILE csv

User · Answer

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   gt  outfile csv

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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    nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp 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

User · Answer

New version - psql 12 -  will support --csv   psql - devel --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  quot SELECT   FROM pg catalog pg tables quot  --csv  postgres  psql -c  quot SELECT   FROM pg catalog pg tables quot  --csv -P csv fieldsep      postgres  psql -c  quot SELECT   FROM pg catalog pg tables quot  --csv  postgres  gt  output csv

User · Answer

There are several solutions  1 psql command psql -d dbname -t -A -F quot   quot  -c  quot select   from users quot   gt  output csv This has the big advantage that you can using it via SSH  like ssh postgres host command - enabling you to get 2 postgres copy command COPY  SELECT   from users  To   tmp output csv  With CSV  3 psql interactive  or not   gt psql dbname psql gt  f     psql gt  a psql gt  o   tmp output csv  psql gt SELECT   from users  psql gt  q  All of them can be used in scripts  but I prefer  1  4 pgadmin but that s not scriptable

User · Answer

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  gt    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

User · Answer

To Download CSV file with column names as HEADER use this command   Copy  Select   From tableName  To   tmp fileName csv  With CSV HEADER

User · Answer

CSV Export Unification  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   Remember just one command   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    gt  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    gt  groups csv   It s even great on the local machine     psql -d mydb -c  COPY groups TO STDOUT WITH CSV HEADER   gt  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   gt  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    gt  groups csv   So versatile  much commas   Do you even   Yes I did  here are my notes   The COPYses  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   PSQL Parameters  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     Other Tools  No  I just want to get CSV out of my server without compiling and or installing a tool

[sql] Save PL/pgSQL output from PostgreSQL to a CSV file

Examples related to sql

Examples related to postgresql

Examples related to csv

Examples related to postgresql-copy