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 include aligned (default), wrapped (wraps long values), html (HTML output), and latex (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.
• \copy then exports this formatted data to standard output (STDOUT) with csv format and HEADER 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 and row_to_string with data from unnest.
• 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.