Often in PostgreSQL you may want to pad numbers in a column with leading zeros.
You can use the LPAD function with the following syntax to do so:
SELECT LPAD(amount::text, 6, '0') AS amount_padded
FROM sales;
This particular example will pad the numbers in the amount column of the sales table with enough leading zeros such that the length of the number is equal to 6.
The results will be saved to a new column named amount_padded.
Note that we first cast the numeric amount column to a text field so that we’re able to pad the number with zeros without any errors.
Note: You can also use the RPAD function to instead pad a number with trailing zeros.
The following example shows how to use this syntax in practice.
Example: How to Pad Numbers with Leading Zeros in PostgreSQL
Suppose that we create the following table named sales that contains information about the total number of sales made by various retail stores:
-- create table named 'athletes'
CREATE TABLE sales (
store TEXT,
amount INTEGER
);
-- add rows to the table
INSERT INTO sales VALUES ('A', 130);
INSERT INTO sales VALUES ('B', 290);
INSERT INTO sales VALUES ('C', 1430);
INSERT INTO sales VALUES ('D', 447);
INSERT INTO sales VALUES ('E', 876);
INSERT INTO sales VALUES ('F', 2305);
INSERT INTO sales VALUES ('G', 99);
INSERT INTO sales VALUES ('H', 186);
-- view all rows in table
SELECT * FROM sales;
store | amount
-------+--------
A | 130
B | 290
C | 1430
D | 447
E | 876
F | 2305
G | 99
H | 186
Suppose that we would like to pad each of the numbers in the amount column such that each number in the column has a length of 6.
We can use the following syntax to do so:
SELECT *,
LPAD(amount::text, 6, '0') AS amount_padded
FROM sales;
store | amount | amount_padded
-------+--------+---------------
A | 130 | 000130
B | 290 | 000290
C | 1430 | 001430
D | 447 | 000447
E | 876 | 000876
F | 2305 | 002305
G | 99 | 000099
H | 186 | 000186
Notice that the resulting table contains a new column named amount_padded that contains the numbers from the amount column padded with enough leading zeros that the length of each number is 6.
Feel free to use similar syntax to pad numbers with leading zeros in your own field.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Extract Numbers from a String
PostgreSQL: How to Round Numbers Up to Nearest Integer
PostgreSQL: How to Round Numbers to 2 Decimal Places
