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

Question

I cannot see leading / trailing spaces in the following psql SQL statement:

 select name from my_table; 

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

Answer 1

If you do not mind substituting all the space characters, regardless of whether they are kept / ending, then do the following:

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

This underline emphasizes spaces, but of course you can choose your own. See the SQL script demo .

If you strictly want to display leading / trailing, it will be more complex, but if it is really necessary, something is possible with regex_replace .

Answer 2

One option is to use the format () function.

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

PoC:

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

Answer 3

Disable "aligned mode" in psql: \a

 \a select * from my_table; id|col1|col2 12|foo|bar 

Answer 4

I would add quotes:

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