How to hide result set decoration in Psql output

Advanced customization

Using COPY for no-frills output

The COPY command in PostgreSQL, when used with -t or --tuples-only, can output results unadorned with headers or footers:

-- If this were Ninjago, COPY just went Spinjitzu on your query results.
COPY (SELECT * FROM your_table) TO '/absolute/path/to/result.txt' WITH CSV HEADER;


Ensure the path is absolute to prevent any directory confusion. Also, remember, COPY command requires superuser privileges or being the table owner.

The -c and --output psql ninjas

For situations when a specific query should be executed from the command line, -c is your go-to option:

-- Don't forget to invite your database and user to the ninja party.
psql -d database_name -U user_name -c "SELECT * FROM your_table" --output=result.txt


Using the --output option will send your query results to a file, all without inviting "extra formatting" to your party.

The -A and -F combo for precision strikes

Use the -A option for an unaligned output and -F to specify a field separator. A combo move like the below makes CSV-style output your ally:

-- Comma: "Why am I always the separator?"
-- -A and -F: "Because you're good at keeping things apart."
psql -A -F , -c "SELECT * FROM your_table"


Scripting your way through

When built-in options don't meet your requirements, let bash scripting step in. Use it to parse and format the psql output:

-- Linux: "I got 99 problems, but lack of scriptability ain't one."
psql -d mydb -t -A -F"," -c "SELECT * FROM my_table" | while IFS=, read col1 col2; do
  echo "Column1: $col1"
  echo "Column2: $col2"
done


Getting column names on the fly

For times when you need to retrieve column names dynamically, use information_schema.columns:

-- information_schema.columns: "Oh, you forgot column names again?"
SELECT column_name FROM information_schema.columns WHERE table_schema = 'schema_name' AND table_name = 'table_name';


Best practices

Automate with .psqlrc

Make your life easier and automate settings in your .psqlrc file. For example, to always hide row count:

\pset footer off


Learning from examples

Understanding in-depth control over psql can be achieved by diligently studying command line examples. Here's an example that shows querying, output file, and formatting in one line:

-- Learning from examples is like downloading someone else's wisdom.
psql -d mydb -U myuser -c "SELECT * FROM mytable" --output=myoutput.txt -A -F";"


Knowing your tools well

Spend some quality time with PostgreSQL documentation to unlock hidden features and grasp best practices.