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

I can’t see leading/trailing whitespace in the following following SQL statement executed with psql:

select name from my_table;

Is there a pragmatic way to see leading/trailing whitespace?

Answer

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.