PostgreSQL regexp_replace() to keep just one whitespace

SELECT trim(regexp_replace(col_name, '\s+', ' ', 'g')) as col_name FROM table_name;

Or In case of update :

UPDATE table_name SET col_name = trim(regexp_replace(col_name, '\s+', ' ', 'g'));

SELECT trim(regexp_replace(mystring, '\s+', ' ', 'g')) as mystring FROM t1;

Posting an answer in case folks don't look at comments.

Use '\s+'

Not '\\s+'

Worked for me.

It didn't work for me with trim and regexp_replace. So I came with another solution:

SELECT trim(    array_to_string(        regexp_split_to_array('  test    with many  spaces  for        this   test  ', E'\\s+')    , ' ')) as mystring;

First regexp_split_to_array eliminates all spaces leaving "blanks" at the beginning and the end.

-- regexp_split_to_array output:-- {"",test,with,many,spaces,for,this,test,""}

When using array_to_string all the ',' become spaces

-- regexp_split_to_array output ( '_' instead of spaces for viewing ):-- _test_with_many_spaces_for_this_test_

The trim is to remove the head and tail

-- trim output ( '_' instead of spaces for viewing ):-- test_with_many_spaces_for_this_test