Lpad Function in Postgres SQL

Introduction

Here we learn in this tutorial session to use the Lpad function to achieve various text- and integer- related tasks in Postgres SQL, including:

• What? What is the syntax of the LPAD function and what does the function do? How do we use the Lpad() function in our PostgreSQL queries? What parameters does it use?
• Why? When do we need this function in our queries?
• Extras We’ll also get exposure to basic SQL commands including SELECT, FROM, and WHERE. We’ll also work some with the ARRAY type, the ARRAY_LENGTH function, the WHILE loop, RAISE NOTICE, and how to cast an integer as text.

Prerequisites

• Basic knowledge of how to create and use SQL queries with Postgres (or MS SQL Server, MySQL, Oracle, etc.), using PostgreSQL’s free database admin tool or another relational database administration tool, or with code or script languages like PHP, Python, Javascript, Java, C#, ASP.Net, VB.Net, Note.js, Ruby, etc.) that allows a connection to your database as well as a method for sending SQL commands or requests to query or make changes to a database.
• Knowledge of the use of basic SQL statements, including SELECT, FROM, and WHERE.
• Understanding of what the following terms mean: text types, strings, and integers.

What does the LPAD function do in Postgres SQL?

We use PostgreSQL’s LPAD function to “pad” a string with a specific number of characters to obtain a resulting pre-determined string (text) length. As we’ll see in our second example below, sometimes we want to pad numbers with zeros in order to obtain uniformity.

How to use this function with SQL and parameters

Syntax of LPAD() function

We give the LPAD function three parameters:

• t_source: The string we feed to our hungry function.
• i_result_length: Tells LPAD() how many characters we want the resulting string to be in length.
• t_padding_char: Determines which character to use for padding. Most often, this character is set to be “0” or ” “.

Let’s use the syntax above and take a step in the direction of more complexity and more understanding. The mini-mission is to take four words and “pad” all of them on their left so that they are all the same length, appearing as if they are right justified.

Analysis of the above code

• DECLARE: Here we (1) Set up an array to hold our four words and name this array “a_technologies”. (2) We’ve determined that the longest word in our array is “Javascript”, which is 10 characters long. We want to pad all words in our array so they end up being 10 characters long. This is why we assigned “10” to “i_result_length”. (3) For “t_padding_char” we chose a space, so that spaces are placed at the beginning of each of the words in our array until that word is a total of 10 characters in length. (4) Here we are initializing “i_current_array_spot” to be an integer of zero. This variable will be used to count through our WHILE loop, pick which word we are currently working on, and end the loop. (5) “i_size_of_array” is used in our loop below to tell PostgreSQL when to exit the loop.
• WHILE: Here we are setting up our loop to cycle from 0 to 3.
• LOOP: Just below this line, we increase “i_current_array_spot” by 1, so that the actual array item being pulled is one higher than 0 to 3; becoming 1 to 4.
• t_result: This is the meat of our program. We’re plugging all our variables into LPAD in order to get t_result.
• RAISE NOTICE: Here we return “t_result” to see the following output.

Notice how each word was “padded” to a maximum of 10 characters, pushing all of them to have a uniform right margin?

Let’s explore another common – possibly more common – usage of the LPAD() function in Postgres SQL. We have a table with integers ranging widely in the number of decimal places and want to pad these numbers with zeros so they have a uniform appearance.

Let’s start with the following database table we’ve named “technologies”.

Analysis

• SELECT t_name_tech: For reference, we are “printing” the columns named “t_name_tech” and “t_category_tech”.
• LPAD: We fed the LPAD() function with three parameters: “i_rating” is the source, “6” is the maximum number of characters we want to get to, and “0” is the character we want PostgreSQL to pad with. NOTICE the “::text” after “i_rating”? The LPAD function requires a TEXT type, the “i_rating” column is an INTEGER, so we use “::text” to cast our integer as a string!
• AS: Here we are naming our resulting string as a new column, “t_rating_padded”.
• FROM: tells the Postgres database engine which table to use as the source for our SQL data.

Here are the results when we run our query:

Miscellaneous Notes

• In some situations, like with arrays in Python and many other languages, we start counting at zero. So it is important to keep in mind that with LPAD() we begin counting size at the value of one, not zero. Same goes with Postgres arrays, as you saw above in our WHILE loop example.
• Did you notice we named some columns and variables with a prefix of “i” or “t“? In this tutorial, we used “i” to mean integer and “t” to mean text or string. Here is an article that explains why, including how the practice can increase your efficiency as a programmer.

Conclusion

In this lesson we learned how to use the LPAD() function in our Postgres SQL, including showing a couple ways we might do that. We also practiced the use of SQL commands including SELECT, FROM, and WHERE. We worked with an ARRAY type, the ARRAY_LENGTH function, the WHILE loop, RAISE NOTICE, and learned how to convert an integer into a text string.