Padding zeros to the left in postgreSQL

Question

I am relatively new to PostgreSQL and I know how to pad a number with zeros to the left in SQL Server but I m struggling to figure this out in PostgreSQL   I have a number column where the maximum number of digits is 3 and the min is 1  if it s one digit it has two zeros to the left  and if it s 2 digits it has 1  e g  001  058  123   In SQL Server I can use the following   RIGHT  000    cast  Column1  as varchar 3    3  as  Column2    This does not exist in PostgreSQL  Any help would be appreciated

User · Answer

As easy as  SELECT lpad 42  text  4   0     References    http   www postgresql org docs current static functions-string html   sqlfiddle  http   sqlfiddle com   15 d41d8 3665

User · Answer

The to char   function is there to format numbers   select to char column 1   fm000   as column 2 from some table    The fm prefix   fill mode   avoids leading spaces in the resulting varchar  The 000 simply defines the number of digits you want to have    psql  9 3 5  Type  help  for help   postgres  gt  with sample numbers  nr  as   postgres  gt      values  1   11   100  postgres  gt    postgres- gt  select to char nr   fm000   postgres- gt  from sample numbers   to char ---------  001  011  100  3 rows   postgres  gt   For more details on the format picture  please see the manual   http   www postgresql org docs current static functions-formatting html

User · Answer

You can use the rpad and lpad functions to pad numbers to the right or to the left  respectively  Note that this does not work directly on numbers  so you ll have to use   char or   text to cast them   SELECT RPAD numcol  text  3   0    -- Zero-pads to the right up to the length of 3        LPAD numcol  text  3   0    -- Zero-pads to the left up to the length of 3 FROM   my table

[sql] Padding zeros to the left in postgreSQL

Examples related to sql

Examples related to postgresql

Examples related to zero-padding