Viewed 3k times. Improve this question. Have your tried anything? Which OS you are using? Show 3 more comments. Active Oldest Votes. Improve this answer. Add a comment. You could do it using a bash script. Bill Doughty Bill Doughty 2, 13 13 silver badges 20 20 bronze badges. I like Abelisto's solution much better, if it works. I think he should make it an answer, instead of comment. Adrian Mole Andrew Andrew 36 2 2 bronze badges. Note i'm at the psql command line when I try this.
Active 3 days ago. Viewed k times. I'm using PostgreSQL 8. Improve this question. Erwin Brandstetter k gold badges silver badges bronze badges. Hoff Hoff See also stackoverflow. Add a comment. Active Oldest Votes. Do you want the resulting file on the server, or on the client? This might be a particular directory, for instance, and the filename might have to have a suitable prefix or extension.
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. Client side The other approach is to do the file handling on the client side , i.
Improve this answer. Lukas Vrabel 8 8 silver badges 15 15 bronze badges. See my edit. So the query gets saved to a. It won't work in other clients, like pgAdmin; they will probably have their own tools, such as graphical wizards, for doing this job. Show 8 more comments. Community Bot 1 1 1 silver badge. IMHO the first option is error prone, because it doesn't include proper escaping of comma in exported data.
Also, psql doesn't quote cell values, so if ANY of your data uses the delimiter, your file will be corrupted. Cerin -t is a synonym for --tuples-only turn off printing of column names and result row count footers, etc. Show 11 more comments. Marcin Wasiluk Marcin Wasiluk 4, 3 3 gold badges 32 32 silver badges 43 43 bronze badges. This will not produce a CSV file, it will just record the command output to the text file which does not make it the comma-separated.
RuslanKabalin yes I have just notticed that and ammended instruction to create comma-separated output cvs — Marcin Wasiluk.
I'd improve this answer by noting that the "csv" output will not be properly escaped and each time a sql command is executed the results are concatenated to the output file. What about newlines in field values? Show 1 more comment.
Do you even? Where do the results get saved to? My query runs but the file doesn't show up anywhere on my computer. You want to make sure that is outside the command being sent to to the server through the psql -c parameter.
See the 'local machine' example. Thanks for the complete explanation. The copy command is hopelessly complex with psql. I end up usually using a free database client dbeaver community edition to import and export data files.
It provides nice mapping and formatting tools. Your answer provides great detailed examples for copying from remote systems. This is an amazing solution. Thanks a lot. If the query is custom I. New version - psql 12 - will support --csv. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters. Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results.
They are not the utmost best solution s. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals.
Opinions are my own. Originally published at joshuaotwell. Growing through Technical Writing and Blogging. Sign-up for my free newsletter here: openlamptech. Bursts of code to power through your day. Web Development articles, tutorials, and news. Sign in. Utility psql command for saving query output to a file.
0コメント