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

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
DECLARE
    a_technologies TEXT[4];
    a_technologies := {"Postgres", "MS SQL", "Javascript", "Python"};
    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
BEGIN
-- loop through our four phrases
    WHILE i_current_array_spot < i_size_of_array
        LOOP
            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".
        END LOOP;
END

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.
  Postgres
    MS SQL
Javascript
    Python

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”.

t_name_techt_category_techi_rating
JavaLanguage4
MongoNoSQL15
MySQLDatabase5
PostgreSQLDatabase173
PythonLanguage1432
PHPLanguage12
JavascriptLanguage300
SELECT
    t_name_tech
    , t_category_tech
    , LPAD(i_rating::text, 6, "0") AS t_rating_padded
FROM
    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:

t_name_techt_category_techt_rating_padded
JavaLanguage000004
MongoNoSQL000015
MySQLDatabase000005
PostgreSQLDatabase000173
PythonLanguage001432
PHPLanguage000012
JavascriptLanguage000300

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.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.