5 Ways to Make PostgreSQL Query Results More Readable

If you often work with PostgreSQL queries, you know that sometimes the raw output can be difficult to read, especially when you’re dealing with long queries or large datasets.

Fortunately, PostgreSQL provides several ways to format and organize your query results for better readability.

In this article, we will explore five practical methods to make your PostgreSQL query output clearer, more organized, and easier to interpret.

Enable Expanded Mode (\x) for Better Formatting in psql

One of the most effective ways to improve the readability of query results is to use expanded mode in the psql command-line tool.

When expanded mode is enabled, each row’s fields are displayed on separate lines. This is particularly useful for queries that return long text fields, such as the query column in pg_stat_activity.

How to Use Expanded Mode:

\x
SELECT pid AS process_id, query, query_start FROM pg_stat_activity;

Example Output:

-[ RECORD 1 ]---------
process_id | 12345
query      | SELECT * FROM my_table WHERE id = 1;
query_start| 2024-11-18 12:00:00
-[ RECORD 2 ]---------
process_id | 12346
query      | SELECT * FROM another_table;
query_start| 2024-11-18 12:01:00
    

This method is especially helpful when dealing with long queries, as each field is printed on a separate line, making it much easier to read.

Use Table Format for Proper Column Alignment

If you prefer to see your query results in a clean, tabular format, you can use the \pset command in psql to set the output to aligned format. This will ensure that columns are neatly aligned, which makes it easier to interpret the results.

How to Set Table Format:

\pset format aligned
SELECT pid AS process_id, query, query_start FROM pg_stat_activity; 

Example Output:

 process_id | query                         | query_start         
-------------+-------------------------------+---------------------
      12345 | SELECT * FROM my_table WHERE ... | 2024-11-18 12:00:00
      12346 | SELECT * FROM another_table     | 2024-11-18 12:01:00
    

This approach is great for small to medium-sized result sets where proper column alignment enhances readability.

Limit the Length of the Column in Postgresql

When your queries contain long text strings, such as SQL statements in the query column of the pg_stat_activity table, truncating the text can significantly improve the clarity of the output.

Use the LEFT() function to limit the number of characters displayed from the query.

How to Limit Query Length:

SELECT pid AS process_id, LEFT(query, 100) AS query, query_start 
FROM pg_stat_activity;

Example Output:

 process_id | query                          | query_start         
-------------+---------------------------------+---------------------
      12345 | SELECT * FROM my_table WHERE ... | 2024-11-18 12:00:00
      12346 | SELECT * FROM another_table     | 2024-11-18 12:01:00
    

This method ensures that the output remains concise, even when the query text is lengthy.

Export Results to CSV for External Analysis

For larger query results or when you need to further analyze the data in external tools like Excel, exporting the results to a CSV file is a great option. You can use the \copy command in psql to export the query output to a CSV file.

How to Export to CSV:

\copy (SELECT pid AS process_id, query, query_start FROM pg_stat_activity) TO 'output.csv' WITH CSV HEADER;
    

This command will export the query results to a file named output.csv, which you can later open and analyze in a spreadsheet application.

Customize Output Formatting with TO_CHAR()

Sometimes you may need to format specific columns, like date fields or numbers, for easier readability. PostgreSQL’s TO_CHAR() function allows you to format dates and numbers according to your preferred style.

How to Format Date and Time Columns:

SELECT pid AS process_id, 
       LEFT(query, 100) AS query, 
       TO_CHAR(query_start, 'YYYY-MM-DD HH24:MI:SS') AS query_start
FROM pg_stat_activity;
    

Example Output:

 process_id | query                          | query_start         
-------------+---------------------------------+---------------------
      12345 | SELECT * FROM my_table WHERE ... | 2024-11-18 12:00:00
      12346 | SELECT * FROM another_table     | 2024-11-18 12:01:00
    

By formatting the query_start column with a readable date and time format, you make the output more user-friendly and easier to interpret.

Conclusion

By using the methods outlined above, you can significantly improve the readability of your PostgreSQL query results. Whether you’re working with long queries, large datasets, or complex date formats, these tips will help you organize your output for better clarity and efficiency.

• Expanded Mode (\x): Use for long text columns to display results on separate lines.
• Table Format (\pset format aligned): Align columns in a clean table format for short results.
• Limit Column Length: Use LEFT() to truncate long text columns like query.
• Export to CSV: Use \copy to export results for external analysis.
• Customize SQL Output: Use functions like TO_CHAR() to format date and text columns as needed.

Choose the best formatting options for your needs, and you’ll be able to work more efficiently with PostgreSQL data.