Save PL/pgSQL output from PostgreSQL to a CSV file

Generate CSV on the fly with psql

The psql command line offers nifty one-liners that enable you to generate CSV outputs quickly:

psql -d dbname -t -A -F"," -c "select * from users" > output.csv


Need a cleaner output without headers? Use this trick:

psql -P pager=off -d mydb -t -A -F',' -c 'select * from table;' > table.csv


These one-liners instantly export the results of your queries, perfect for immediate data insights.

Security considerations: Superuser and access permissions

Please note, direct exporting via COPY TO demands superuser rights. To better manage the security while executing such operations, wrap the command in a SECURITY DEFINER function, as below:

CREATE FUNCTION export_users_csv() RETURNS void LANGUAGE plpgsql SECURITY DEFINER AS $$
BEGIN
  COPY (SELECT * FROM users) TO '/safe/path/users.csv' WITH CSV HEADER;
  -- "Root privileges? Never heard of 'em." - This function, probably.
END;
$$;


This practice ensures proper permission enforcement. Recall to set appropriate file and data access permissions before executing the export function.

Utilizing GUI: pgAdmin to the rescue

For those who prefer a GUI, pgAdmin is a great tool. Follow these steps to export your data:

• Find the table you wish to export
• Right-click it and select the Export option
• Choose the CSV format and set your preferences

Spiffy GUI for painless data shapeshifting.

Flexibility: Superuser or not, you're covered

Working in an environment that is strict about superuser privileges? The \copy command becomes your ally in such cases. It bypasses the superuser requirements, giving you the optimum blend of power and compliance:

psql -c "\copy (SELECT * FROM table) TO '/path/to/file.csv' WITH CSV"


In the PHP realm, you'd employ pg_copy_from and pg_copy_to to accomplish your exporting tasks:

pg_copy_to($connection, 'tablename', '/path/to/file.csv');
// This line is literally the "Copy-Pasta" chef of PHP 🍝.


When it comes to large exports or remote operations, COPY can be executed using Docker or Kubernetes. This approach scales better and maintains connectivity efficiently.

Remote execution: SSH your PostgreSQL commands

You are not limited to your local machine, step into the realm of remote executions with SSH:

ssh postgres@host 'psql -c "QUERY" --csv' > output.csv


You can also use a sequence of psql interactive commands to generate a CSV output:

psql -d dbname -c "\f ',' \a \o '/tmp/output.csv' SELECT * FROM table;"


Customized exports: Your way, all the way

In certain cases, you may need to customize the delimiter or require an immediate output. No worries, you can set your specific preferences with --csv flag of psql as follows:

psql -c "COPY (SELECT * FROM table) TO STDOUT WITH CSV DELIMITER '|'" > output.csv


Simply use --csv flag for an immediate output:

psql -c "QUERY" --csv > output.csv
