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)