How to show leading/trailing whitespace in a PostgreSQL column?

One of option is to use format() function.

With given query case: select format( '"%s"', name ) from my_table;

PoC:

SELECT  format( '"%s"', name )
FROM    ( VALUES ( ' a ' ), ( ' b ' ) ) v(name);

 format
--------
 " a "
 " b "
(2 rows)

If you don't mind substituting all whitespace characters whether or not they are leading/trailing, something like the following will do it:

SELECT REPLACE(REPLACE(REPLACE(REPLACE(txt, ' ', '_'),
                               E'\t', '\t'),
                       E'\r', '\r'),
               E'\n', '\n') AS txt
FROM test;

This is using an underscore to mark the spaces but of course you are free to choose your own. See SQL fiddle demo.

If you strictly only want to show up the leading/trailing ones it will get more complex - but if this is really desired, something may be possible using regex_replace.

I'd append surrounding quotes:

select '"' || name || '"' from my_table;

Turn off "aligned mode" in psql: \a

\a
select * from my_table;

id|col1|col2
12|foo|bar