Save PL/pgSQL output from PostgreSQL to a CSV file
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
GRANT
s 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 DELIMITER ',' HEADER
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.
How to export a PostgreSQL query output to a csv file
Modern syntax:
COPY (SELECT * FROM ...) TO '/tmp/filename.csv' (FORMAT csv);
So the 162 rows of my output table have been copied in the shell. How
can I paste or move them to a csv file?
The result is the CSV file. Open it with any spreadsheet program using matching delimiters. The manual:
The default is a tab character in text format, a comma in CSV format
The psql meta command \copy
is a wrapper around the SQL COPY
function. It writes and reads files local to the client (while COPY
uses files local to the server) and does not require superuser privileges.
See:
- Export specific rows from a PostgreSQL table as INSERT SQL script
- PostgreSQL: export resulting data from SQL query to Excel/CSV
Saving psql output to csv file
It is not explained in the documentation, but the -F
option requires the -A
option (unaligned table output) to work:
psql -U username -d dbname -f /path/to/query -o /path/to/output/file -F ',' -A
If you don't wish the headers in your csv, this means, without extra rows at the top and at the bottom, use the -t
option too.
psql -U username -d dbname -f /path/to/query -o /path/to/output/file -F ',' -A -t
From the help:
-A, --no-align unaligned table output mode
-F, --field-separator=STRING
set field separator (default: "|")
-t, --tuples-only print rows only
psql - save results of command to a file
From psql's help (\?
):
\o [FILE] send all query results to file or |pipe
The sequence of commands will look like this:
[wist@scifres ~]$ psql db
Welcome to psql 8.3.6, the PostgreSQL interactive terminal
db=>\o out.txt
db=>\dt
Then any db operation output will be written to out.txt.
Enter '\o' to revert the output back to console.
db=>\o
How to export table as CSV with headings on Postgresql?
COPY products_273 TO '/tmp/products_199.csv' WITH (FORMAT CSV, HEADER);
as described in the manual.
PostgreSQL - export output to CSV
you can use terminal command like this :
COPY ( your query inside ) TO 'C:\tmp\persons_email_db.csv' DELIMITER ',' CSV;
or you can simply use some Database Tools like navicat or dbeaver to run your query above and export the result to csv
Related Topics
How to Change the MySQL Root Password
SQL Join - Where Clause Vs. on Clause
Postgresql Unnest() With Element Number
How Stuff and 'For Xml Path' Work in SQL Server
How to Reset Auto_Increment in MySQL
Sql, Auxiliary Table of Numbers
Get Records With Max Value For Each Group of Grouped SQL Results
Difference Between Lateral Join and a Subquery in Postgresql
How to Insert Multiple Rows At a Time in an Sqlite Database
MySQL How to Fill Missing Dates in Range
Identity Increment Is Jumping in SQL Server Database
How to Request a Random Row in Sql
Simple Way to Transpose Columns and Rows in Sql