This post is older than a year. Consider some information might not be accurate anymore.
Used: postgresql 8.4
psql has the capability to export the output to files. This is a write-up for it. I got several use cases:
- I needed to export the database tables to a file for further processing.
- I needed the query results as interface file for another system.
- I needed the results as LaTeX table for my documentation in LaTeX.
Formats
Various formats are supported. Allowed formats are
- unaligned,
- aligned,
- wrapped,
- html, latex, troff-ms.
Unaligned
The unaligned format writes all columns of a row on one line, separated by the currently active field separator. This is useful for creating output that might be intended to be read in by other programs (for example, tab-separated or comma-separated format).
Aligned
The aligned format is the standard, human-readable, nicely formatted text output; this is the default.
Wrapped
The wrapped format is like aligned but wraps wide data values across lines to make the output fit in the target column width. The target width is determined as described under the columns option. Note that psql will not attempt to wrap column header titles; therefore, wrapped format behaves the same as aligned if the total width needed for column headers exceeds the target.
Misc
The html, latex, and troff-ms formats put out tables that are intended to be included in documents using the respective mark-up language.
Set Output Format
In the psql console.
\pset format <format>
Sets the output format to one of unaligned, aligned, wrapped, html, latex, or troff-ms. Unique abbreviations are allowed. (That would mean one letter is enough.)
Export to a file
The general syntax is:
\o [FILE] send all query results to file or |pipe
Example: Store query results in results.txt. p01 is the name of the production database.
p01=> \o results.txt
p01=> select customer_id, total_amount from transactions;
Export as LaTeX Table
The default formatting in psql is aligned. Output the table contents in LaTeX. This makes documentation with LaTeX much easier.
p01=> \pset format latex
Output format is latex.
p01=> select * from transactions;
\begin{tabular}{r | l | r | l | l | l}
\textit{nummer} & \textit{konto} & \textit{betrag} & \textit{kostenstelle} & \textit{kostentraeger} & \textit{transaktion} \\
\hline
2 & 197450) & 34.9600 & 469950 & 2555180 & 110545330 \\
1 & 197450) & 34.9600 & 469950 & & 110545330 \\
\end{tabular}
\noindent (2 rows) \\
