Viewing PostgreSQL Data in a Specific Format: Column-Wise with Names
psql is a powerful command-line tool that allows you to interact with PostgreSQL databases. It provides various features for executing SQL queries, managing database objects, and viewing results.
Output Formatting Options
While psql's default output displays data in a tabular format with rows and columns, you can customize the presentation for better readability or specific use cases.
Method 1: Using the \x
Command
- The
\x
command in psql is a quick and effective way to toggle the output format. - When you enter
\x
at the psql prompt, it switches the output to display each column name followed by its corresponding data values, one per line. - To revert back to the default tabular format, simply type
\x
again.
Example:
psql> \x -- Turn on one-column-per-line output
psql> SELECT * FROM your_table;
column_name1: value1_row1
column_name1: value1_row2
column_name2: value2_row1
column_name2: value2_row2
... (continues for all columns and rows)
psql> \x -- Turn off one-column-per-line output (back to default)
- The
\pset
command provides more fine-grained control over psql's output formatting. - To achieve the one-column-per-line format with
\pset
, use the following syntax:
psql> \pset format unaligned
- The
unaligned
option instructs psql to print each column's data values on separate lines, without aligning them.
Understanding the unaligned
Option:
unaligned
is one of several formatting options available with\pset
. Other options includealigned
(default),wrapped
(wraps long values),html
(HTML output), andlatex
(LaTeX output).unaligned
is particularly useful for this scenario because it ensures each column appears on its own line.
Key Points:
- Both
\x
and\pset format unaligned
achieve the desired one-column-per-line output with column names. \x
is a simpler, one-time toggle, while\pset
allows for more permanent configuration changes (until you reset it).- Consider your workflow and preferences when choosing the method.
psql> -- Connect to your PostgreSQL database (replace with your connection details)
psql -h your_host -p your_port -U your_username your_database_name
psql> -- Assuming you have a table named 'products' with columns 'product_id', 'name', and 'price'
psql> SELECT * FROM products;
+------------+----------+-------+
| product_id | name | price |
+------------+----------+-------+
| 1 | Shirt | 29.99 |
| 2 | Pants | 39.95 |
| 3 | Hat | 14.50 |
+------------+----------+-------+
psql> -- Toggle to one-column-per-line output
psql> \x
psql> -- Now the output will be:
product_id: 1
product_id: 2
product_id: 3
name: Shirt
name: Pants
name: Hat
price: 29.99
price: 39.95
price: 14.5
psql> -- Toggle back to default tabular output
psql> \x
psql> -- Now the output will return to the original format
psql> -- Connect to your PostgreSQL database (replace with your connection details)
psql -h your_host -p your_port -U your_username your_database_name
psql> -- Assuming you have a table named 'products' with columns 'product_id', 'name', and 'price'
psql> SELECT * FROM products;
+------------+----------+-------+
| product_id | name | price |
+------------+----------+-------+
| 1 | Shirt | 29.99 |
| 2 | Pants | 39.95 |
| 3 | Hat | 14.50 |
+------------+----------+-------+
psql> -- Set the output format to 'unaligned'
psql> \pset format unaligned
psql> -- Now the output will be:
product_id: 1
product_id: 2
product_id: 3
name: Shirt
name: Pants
name: Hat
price: 29.99
price: 39.95
price: 14.5
psql> -- To revert back to default format, you can use:
psql> \pset format aligned
- The
\copy
command is used to export data from a PostgreSQL table to a file. However, with some clever formatting, it can be used to achieve the desired output within psql itself.
psql> \copy (SELECT column_name, row_to_string(t.*) as row_data
FROM (SELECT column_name, * FROM information_schema.columns
WHERE table_name = 'your_table') AS cols,
unnest(your_table) AS t(col1, col2, ...)) TO STDOUT WITH (FORMAT csv, HEADER TRUE);
Explanation:
- This approach leverages several SQL features:
- A subquery retrieves column names from
information_schema.columns
for the specified table (your_table
). unnest
unpacks each row of your table (your_table
) into separate columns (col1
,col2
, ...).- The main query combines column names and row data using
row_to_string
.
- A subquery retrieves column names from
\copy
then exports this formatted data to standard output (STDOUT
) withcsv
format andHEADER TRUE
to include the column names.
Using a Custom Function:
- You can create a custom function that takes a table name and returns the data in the desired format.
CREATE OR REPLACE FUNCTION get_column_data(table_name text)
RETURNS text AS $$
DECLARE
record record;
column_name text;
BEGIN
FOR record IN SELECT * FROM information_schema.columns WHERE table_name = $1 LOOP
column_name := record.column_name;
EXECUTE format('%s: %s', column_name, (SELECT row_to_string(t.*) FROM unnest($1) AS t(col1, col2, ...)));
END LOOP;
RETURN '';
END;
$$ LANGUAGE plpgsql;
SELECT get_column_data('your_table');
- This function iterates through the table's columns using
information_schema.columns
. - For each column, it constructs a formatted string using
format
androw_to_string
with data fromunnest
. - The function doesn't explicitly return anything, but prints the formatted data within the loop.
- Finally, you call the function with your table name to display the desired output.
postgresql output-formatting psql