Pads a string on the left to the length you need.
LPAD(<text>, <length>)
LPAD(<text>, <length>, <fill>)
Adds characters at the start until the string reaches the required length; without the third argument — spaces. Its mirror function is RPAD, which pads on the right. Most often used so that numbers have the same width: 7 → 0007.
orders
| id | created_at | amount |
|---|---|---|
| 1 | 2026-07-03 10:15:00 | 120.50 |
| 2 | 2026-07-28 18:40:00 | 80.25 |
| 3 | 2026-08-09 09:05:00 | 199.99 |
| 4 | 2026-08-21 14:30:00 | 45.00 |
| 5 | 2026-09-02 11:00:00 | 64.75 |
These tables already exist in the sandbox — the examples below use them. Want to try this in your own database? Copy the SQL.
| padded |
|---|
| 0007 |
| id | order_number |
|---|---|
| 1 | 00001 |
| 2 | 00002 |
| 3 | 00003 |
| 4 | 00004 |
| 5 | 00005 |
LPAD works with text, so we convert the number first: id::text.
| id | order_code |
|---|---|
| 1 | ORD-00001 |
| 2 | ORD-00002 |
| 3 | ORD-00003 |
| 4 | ORD-00004 |
| 5 | ORD-00005 |
LPAD truncates a longer string rather than leaving it as is: LPAD('12345', 3, '0') gives 123.
It only accepts text: LPAD(1, 5, '0') throws function lpad(integer, integer, unknown) does not exist. Cast the type: LPAD(id::text, 5, '0').
For numbers there's another way too: TO_CHAR(1, 'FM00000') gives 00001.