Remove Padding from Postgres Formatting Functions

Earlier today, I was trying to join a table on a formatted string row, but wasn't getting the results I expected. Turns out that my formatting string had blank padding and I discovered "fill mode".

When using postgres formatting functions, like to_char, some of the formatting options include padding in the result. For example, the day format string will be blank padded to 9 chars.

select to_char(current_date, 'day');

  to_char
-----------
 sunday

You can use the "fill mode" (FM) option to remove any leading zeros or blank padding characters by prepending it to your format option:

select to_char(current_date, 'FMday')

 to_char
---------
 sunday

https://www.postgresql.org/docs/current/functions-formatting.html