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
, andWHERE
. We’ll also work some with theARRAY
type, theARRAY_LENGTH
function, theWHILE
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
, andWHERE
. - 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
1 | 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | -- 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.
1 2 3 4 | 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_tech | t_category_tech | i_rating |
---|---|---|
Java | Language | 4 |
Mongo | NoSQL | 15 |
MySQL | Database | 5 |
PostgreSQL | Database | 173 |
Python | Language | 1432 |
PHP | Language | 12 |
Javascript | Language | 300 |
1 2 3 4 5 6 | 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_tech | t_category_tech | t_rating_padded |
---|---|---|
Java | Language | 000004 |
Mongo | NoSQL | 000015 |
MySQL | Database | 000005 |
PostgreSQL | Database | 000173 |
Python | Language | 001432 |
PHP | Language | 000012 |
Javascript | Language | 000300 |
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