How to format a numeric value in Postgresql

Question

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.

Jim Jones · Accepted Answer · 2021-07-23 15:12:33Z

3

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)

answered Jul 23, 2021 at 15:12

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rafs · Accepted Answer · 2024-12-12 16:10:05Z

3

Since the output will be a string, you can use to_char with a format of three 0

select to_char(1,'FM000');
 to_char
---------
 001
(1 row)

FM prefix will avoid padding a space to the left with unsigned inputs (See this and this).

edited Dec 12, 2024 at 16:10

Rafs

83411 silver badges26 bronze badges

answered Jul 23, 2021 at 15:47

JGH

18.2k5 gold badges41 silver badges58 bronze badges

1 Comment

Rafs Over a year ago

If you are using this in a CONCAT, this will append spaces to the left. Use: to_char(col, 'FM000') to avoid any padding.

Collectives™ on Stack Overflow

How to format a numeric value in Postgresql

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related