PostgreSQL Tutorial: LPAD Function

September 19, 2023

Summary: The PostgreSQL LPAD() function pads a string on the left to a specified length with a sequence of characters.

Table of Contents

Syntax

The following illustrates the syntax of the LPAD() function:

LPAD(string, length[, fill])

Arguments

The LPAD() function accepts 3 arguments:

1) string

is a string that should be padded on the left

2) length

is an positive integer that specifies the length of the result string after padding.

Noted that if the string is longer than the length argument, the string will be truncated on the right.

3) fill

is a string used for padding.

The fill argument is optional. If you omit the fill argument, its default value is a space.

Return value

The PostgreSQL LPAD() function returns a string left-padded to length characters.

Examples

Let’s see some examples of using the LPAD() function.

The following statement uses the LPAD() function to pad the ‘*’ on the left of the string ‘PostgreSQL’:

SELECT LPAD('PostgreSQL',15,'*');

The result is:

      lpad
----------------
 *****PostgreSQL
(1 row)

In this example, the length of the PostgreSQL string is 10, the result string should have the length 15, therefore, the LPAD() function pads 5 character * on the left of the string.

See the following customer and payment tables from the sample database:

customer and payment tables

The following statement illustrates how to use the LPAD() function to draw a chart based on the sum of payments per customer.

SELECT first_name || ' ' || last_name fullname,
    SUM(amount) total,
    LPAD('*', CAST(TRUNC(SUM(amount) / 10) AS INT), '*') chart
FROM payment
INNER JOIN customer using (customer_id)
GROUP BY customer_id
ORDER BY SUM(amount) DESC;

The following picture illustrates the result:

PostgreSQL LPAD example

In this example,

First, we added up the payments per each customer using the SUM() function and the GROUP BY clause,
Second, we calculated the length of the bar chart based on the sums of payments using various functions: TRUNC() to truncate the total payments, CAST() to convert the result of TRUNC() to an integer. To make the bar chart more readable, we divided the sum of payments by 10.
Third, we applied the LPAD() function to pad the character (*) based on the result of the second step above.

In this tutorial, you have learned how to use the PostgreSQL LPAD() function to pad characters on the left of a string to a certain length.