Today I Learned

hashrocket A Hashrocket project

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

See More #sql TILs
Looking for help? Hashrocket developers believe that data quality is as important as code quality. We enjoy all the challenges of relational databases, from finding the fastest index, to structuring data to fit the needs of an application. We're eager to share our experiences; check out PG Casts, our series of free PostgreSQL screencasts.