Lpad Function in Postgres SQL
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
LPADfunction 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
WHERE. We’ll also work some with the
RAISE NOTICE, and how to cast an integer as text.
- Knowledge of the use of basic SQL statements, including
- 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
t_result = LPAD(t_source, i_result_length, t_padding_char);
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.
-- variable declarations
i_result_length INTEGER := 10;
t_padding_char TEXT := " ";
i_current_array_spot INTEGER := 0;
i_size_of_array INTEGER := ARRAY_LENGTH(a_technologies,1); -- this calculates to be 4
-- loop through our four phrases
WHILE i_current_array_spot < i_size_of_array
i_current_array_spot := i_current_array_spot + 1;
t_result := LPAD(a_technologies[i_current_array_spot], i_result_length, t_padding_char);
RAISE NOTICE t_result;
-- fun fact: in some other forms of SQL, we use "Print" instead of "Raise Notice".
Analysis of the above code
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”.
, LPAD(i_rating::text, 6, "0") AS t_rating_padded
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:
- 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.
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
WHERE. We worked with an
ARRAY type, the
ARRAY_LENGTH function, the
RAISE NOTICE, and learned how to convert an integer into a text string.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.Get Started