Let's say you have a column in a table that contains whitespace. It's important to recognize that this output does not let you see if there is trailing whitespace.

~]$ psql --username john.doe --dbname mydb --command "select column001 from mytable where id=12345" --expanded
-[ RECORD 1 ]-+-----------------------------------------------------
column001     | foo and bar

For trailing whitespace, it may make sense to use whatever programming language you are using to query the data and wrap the output in quotes to visualize trailing whitespace, which could return something like this where there is trailing whitespace.

column001 = 'foo and bar         '

I first tried replace.

~]$ psql --username john.doe --dbname mydb --command "update mytable set column001 = replace(column001, ' ', '') where id=12345"

Which returned the following. where only the whitespace within text was removed and the trailing whitespace remained. That's no good.

column001 = 'fooandbar         '

I next tried trim.

psql --username john.doe --dbname mydb --command "update mytable set column001 = TRIM(column001) where id=12345"

Which had no effect.

column001 = 'foo and bar         '

If whitespace remains, it may be because of special characters. You can try using regexp_replace.

~]$ psql --username john.doe --dbname mydb --command "update mytable set column001 = regexp_replace(column001, ''[\s\u180e\u200B\u200C\u200D\u2060\uFEFF]+$', '') where id=12345"

I once had this issue and none of the above commands would remove the trailing whitespace because the column type was character without varying, like this where the name column is "character (75)".

~]$ psql --username johndoe --dbname mydb --expanded --command "\d public.mytable"
                                     Table "public.mytable"
     Column      |          Type          |                      Modifiers
-----------------+------------------------+------------------------------------------------------
 id              | integer                | not null default nextval('mytable_id_seq'::regclass)
 name            | character (100)        | not null
Indexes:
    "mytable_pkey" PRIMARY KEY, btree (id)

I altered the table type to be varchar which removed the whitespace.

~]$ psql --username john.doe --dbname mydb --command "alter table mytable alter column name set data type varchar(100)"