When you need to turn 42 into 000042 or line up names in a fixed-width column, LPAD and RPAD are the tools. These are string-padding functions: they extend a value to a target length by adding fill characters on the left or the right. Let's walk through the common scenarios and the traps.
Signature and the basic idea
Both functions take three arguments: the source string, the target length, and the fill string.
SELECT LPAD('42', 6, '0') AS padded_left,
RPAD('John', 10, '.') AS padded_right;
Key rules:
LPAD(string, length, fill) adds fill on the left until the string reaches length.RPAD(string, length, fill) does the same on the right.- The third argument is optional: the default fill is a single space.
- The first argument must be text, so cast numeric columns first:
id::text.
SELECT LPAD(id::text, 6, '0') AS user_code
FROM users
ORDER BY id
LIMIT 5;
Zero-padding ids and invoice numbers
The most common case is human-readable codes with leading zeros. A user id of 7 becomes USR-000007; an order id becomes a fixed-format invoice number.
SELECT id,
'USR-' || LPAD(id::text, 6, '0') AS user_code
FROM users
ORDER BY id;
SELECT o.id,
'INV-2026-' || LPAD(o.id::text, 6, '0') AS invoice_no
FROM orders o
WHERE o.status = 'paid'
ORDER BY o.id;
Why pad with LPAD instead of storing the formatted string? The code is always derived from the numeric id, stays sorted like a number, and costs no extra storage. Formatting is a presentation concern, not a storage one.
Truncation: when the string is longer than the target
The big gotcha: if the source string is longer than length, LPAD and RPAD do not grow the result -- they truncate it to the target length. No error is raised.
SELECT LPAD('1234567', 6, '0') AS clipped;
This matters for counters: once an id reaches seven digits, a six-character field silently loses the leading digit. Guard against it with an explicit width check:
SELECT LPAD(id::text, GREATEST(6, LENGTH(id::text)), '0') AS safe_code
FROM users;
Gotcha: the fill string can be longer than one character, in which case it is repeated and then cut at the character boundary. LPAD('7', 5, 'ab') returns abab7 -- the fill cycles on the left, and the surplus characters are dropped.
Fixed-width columns for exports
Legacy systems and banking formats often demand files with fixed-width columns and no delimiters. Here RPAD left-aligns text and LPAD right-aligns numbers.
SELECT RPAD(u.name, 20, ' ')
|| RPAD(u.country, 2, ' ')
|| LPAD(o.amount::text, 12, '0') AS export_line
FROM users u
JOIN orders o ON o.user_id = u.id
ORDER BY u.id;
Handy patterns:
- Text fields (names, departments) get left-aligned with
RPAD and space fill.
- Numeric fields (amounts, quantities) get right-aligned with
LPAD and zero fill.
- Remember truncation: a long
name will be clipped to 20 characters. If that is unacceptable, check LENGTH(name) up front.
SELECT RPAD(name, 25, ' ') || LPAD(salary::text, 10, ' ') AS row_line
FROM employees
ORDER BY dept, name;
MySQL and ClickHouse differences
The basic LPAD/RPAD syntax matches between PostgreSQL and MySQL, but the details diverge:
- In MySQL all three arguments of
LPAD/RPAD are required -- there is no default fill.
- MySQL also truncates strings that are longer than the target.
- MySQL is looser about types: a number is often coerced to a string implicitly, but writing
CAST(id AS CHAR) is safer.
SELECT LPAD(CAST(id AS CHAR), 6, '0') AS user_code
FROM users;
In ClickHouse the functions are leftPad and rightPad (with LPAD/RPAD aliases), but the logic is the same:
SELECT leftPad(toString(id), 6, '0') AS user_code
FROM users;
Bottom line: LPAD pads on the left, RPAD on the right, defaulting to a space. Cast numbers to text before padding, watch for silent truncation of long values, and reach for these functions to generate codes and fixed-width exports rather than to store already-formatted strings.
When you need to turn
42into000042or line up names in a fixed-width column,LPADandRPADare the tools. These are string-padding functions: they extend a value to a target length by adding fill characters on the left or the right. Let's walk through the common scenarios and the traps.Signature and the basic idea
Both functions take three arguments: the source string, the target length, and the fill string.
-- LPAD pads on the LEFT, RPAD pads on the RIGHT SELECT LPAD('42', 6, '0') AS padded_left, -- '000042' RPAD('John', 10, '.') AS padded_right; -- 'John......'Key rules:
LPAD(string, length, fill)addsfillon the left until the string reacheslength.RPAD(string, length, fill)does the same on the right.id::text.-- Cast numeric ids to text before padding SELECT LPAD(id::text, 6, '0') AS user_code FROM users ORDER BY id LIMIT 5;Zero-padding ids and invoice numbers
The most common case is human-readable codes with leading zeros. A user id of
7becomesUSR-000007; an order id becomes a fixed-format invoice number.-- Build a stable user code like USR-000007 SELECT id, 'USR-' || LPAD(id::text, 6, '0') AS user_code FROM users ORDER BY id; -- Invoice numbers with a year prefix: INV-2026-000123 SELECT o.id, 'INV-2026-' || LPAD(o.id::text, 6, '0') AS invoice_no FROM orders o WHERE o.status = 'paid' ORDER BY o.id;Why pad with
LPADinstead of storing the formatted string? The code is always derived from the numericid, stays sorted like a number, and costs no extra storage. Formatting is a presentation concern, not a storage one.Truncation: when the string is longer than the target
The big gotcha: if the source string is longer than
length,LPADandRPADdo not grow the result -- they truncate it to the target length. No error is raised.-- The string is longer than the target -> it gets TRUNCATED SELECT LPAD('1234567', 6, '0') AS clipped; -- '123456', not '1234567'This matters for counters: once an id reaches seven digits, a six-character field silently loses the leading digit. Guard against it with an explicit width check:
-- GREATEST guarantees the field never silently clips SELECT LPAD(id::text, GREATEST(6, LENGTH(id::text)), '0') AS safe_code FROM users;Gotcha: the fill string can be longer than one character, in which case it is repeated and then cut at the character boundary.
LPAD('7', 5, 'ab')returnsabab7-- the fill cycles on the left, and the surplus characters are dropped.Fixed-width columns for exports
Legacy systems and banking formats often demand files with fixed-width columns and no delimiters. Here
RPADleft-aligns text andLPADright-aligns numbers.-- Fixed-width export line: name(20) + country(2) + amount(12) SELECT RPAD(u.name, 20, ' ') || RPAD(u.country, 2, ' ') || LPAD(o.amount::text, 12, '0') AS export_line FROM users u JOIN orders o ON o.user_id = u.id ORDER BY u.id;Handy patterns:
RPADand space fill.LPADand zero fill.namewill be clipped to 20 characters. If that is unacceptable, checkLENGTH(name)up front.-- Pad employee names and right-align salary for a report SELECT RPAD(name, 25, ' ') || LPAD(salary::text, 10, ' ') AS row_line FROM employees ORDER BY dept, name;MySQL and ClickHouse differences
The basic
LPAD/RPADsyntax matches between PostgreSQL and MySQL, but the details diverge:LPAD/RPADare required -- there is no default fill.CAST(id AS CHAR)is safer.-- MySQL: fill argument is required, cast id explicitly SELECT LPAD(CAST(id AS CHAR), 6, '0') AS user_code FROM users;In ClickHouse the functions are
leftPadandrightPad(withLPAD/RPADaliases), but the logic is the same:-- ClickHouse: leftPad / rightPad SELECT leftPad(toString(id), 6, '0') AS user_code FROM users;Bottom line:
LPADpads on the left,RPADon the right, defaulting to a space. Cast numbers to text before padding, watch for silent truncation of long values, and reach for these functions to generate codes and fixed-width exports rather than to store already-formatted strings.