I had to run a query on a Postgresql database in production and export the data to a CSV file. I couldn’t use Postico.
Enter psql on the Postgresql server. Create a Postgresql view (saves the query to a variable).
CREATE VIEW company_locations AS SELECT * FROM companies WHERE location = 'US';
Run the line below to export the view to a csv and keep the header. It is saved to ‘home/deploy/csv_export.csv’ on the server.
copy (select * from view_1) to /local/path/csv_export.csv csv header;
Use scp (secure copy) to move
csv_export.csv to your computer by typing the line below in your local terminal (not in the PSQL terminal).
scp firstname.lastname@example.org:/home/user/csv_export.csv /Users/admin/desktop