PostgreSQL- LPAD Function

The LPAD() function in PostgreSQL is a powerful tool for padding a string to the left, ensuring it reaches a specified length by filling it with a designated character or characters. This function can be particularly useful in data formatting and report generation.

Let us better understand the LPAD Function in PostgreSQL from this article.

Syntax

LPAD(string, length[, fill])

Parameters:

Let's analyze the above syntax:

• string: The string to be padded on the left.
• length: A positive integer specifying the desired length of the result string after padding.
• fill: The character or characters used to pad the original string. This is an optional argument, and if not specified, the default padding character is a space.

PostgreSQL LPAD Function Examples

To better understand the LPAD() function, let's explore a couple of examples.

Example 1: Basic Padding

The following SQL statement demonstrates how to use the LPAD() function to pad the string 'GeeksforGeeks' with the character '*' on the left, ensuring the total length of the string becomes 15 characters.

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

Output:

Explanation: In this example, the original string 'GeeksforGeeks' is padded with three asterisks ('*') on the left to achieve the specified length of 15 characters.

Example 2: Creating a Chart with LPAD()

The following statement illustrates how to use the LPAD() function to draw a chart based on the sum of payments per customer from the 'customer' and 'payment' table of the sample database:

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;

Output:

Explanation: In this example, the fullname column concatenates the first and last names. The total column sums up the payment amounts per customer. The chart column uses LPAD() to create a simple bar chart where the number of asterisks ('*') represents the sum of payments divided by 10.

Important Points About PostgreSQL LPAD Function

• If the specified length is less than the length of the original string, the LPAD() function will truncate the original string to fit the specified length.
• If the string argument is an empty string, LPAD() will return a string composed entirely of the fill characters up to the specified length.
• The LPAD() function can be combined with 'CAST' to pad numeric data after converting it to a string.
• If the fill argument is omitted, LPAD() defaults to using a space character for padding.