PostgreSQL 18 reference · String Functions

PostgreSQL LPAD() Function

Pad a string on the left to a given length with spaces or another string. Longer strings are cut to that length.

PostgreSQL 18

Create a free account to save this page, take notes and track your progress. Sign in

Syntax

lpad(string, length, fill)

Parameter	Description
`string`	The input text.
`length`	The final length in characters.
`fill` optional	Padding text. Default: `' '`

Text.

Pad and cut

SELECT lpad('7', 3, '0') AS zero_padded, lpad('abcdef', 3) AS cut;

Output

 zero_padded | cut
-------------+-----
 007         | abc
(1 row)

Run this on your computer — see “Get Started” for setup.

Invoice numbers

SELECT lpad(order_id::text, 6, '0') AS invoice_no
FROM orders
LIMIT 2;

Output

 invoice_no
------------
 000001
 000002
(2 rows)

Run this on your computer — see “Get Started” for setup.

RPAD() · Pad a string on the right to a given length with spaces or another string. Longer strings are cut to that length.
FORMAT() · Build a string from a template: %s inserts text, %I a quoted identifier and %L a quoted literal, for safe dynamic SQL.
TO_CHAR() · Format a date, timestamp or number as text with a pattern such as 'DD Mon YYYY' or 'FM999,990.00'.
SQL String Functions · tutorial