sqlpostgresqlpostgispgadmin

How to format a numeric value in Postgresql


I have a set of code that generates a number which can be seen below

SELECT MAX(RIGHT ("node_id",3)::numeric) + 1 as newnum from sewers.structures
    WHERE(
    ST_WITHIN(
        ST_CENTROID((ST_SetSRID(structures.geom, 4326))), 
        ST_SetSRID((SELECT geom FROM sewers."Qrtr_Qrtr_Sections" WHERE "plat_page" = '510A'),4326)) ) and "node_id" != 'PRIVATE' and "node_id" !='PRIV_SAN' and "node_id" !='PRIV_STORM'
    

When I run this it generates a number based on the previously placed values. The out put will be a number that can be up to 3 digits. I want to take an output of less than three digits, and force it into a 3 digit format.

For example, if I generate the number 93 I would like to format it as 093. Same for single digit numbers like 2, I want it to be formated at 002 and so on. However, if it generates a 3 digit number, I want it to keep the same format, so 121 stays as 121.


Solution

  • If I got your question right, you're looking for lpad():

    WITH j (x) AS (
      VALUES (2),(121),(93)
    )
    SELECT lpad(x::text,3,'0') FROM j;
    
     lpad 
    ------
     002
     121
     093
    (3 rows)