In PostgreSQL, to left pad a column values we will be using the LPAD() function. This function pads the left side of a string with a specified character up to a specified length.
Let’s see how to
- Left pad of the column in PostgreSQL using LPAD() function
- Padding Zeros to the left of the column in PostgreSQL
- Left Padding Zeros or String to Numeric column in PostgreSQL table
Syntax of LPAD() in PostgreSQL :
LPAD(string, length, fill)
- string: The original string to be padded.
- length: The total length of the resulting string after padding.
- fill: The character to pad the string with.
Examples
Example 1: Padding Zeros to the left
To left-pad the string ‘PostgreSQL’ with zeros (‘0’) to a total length of 15:
SELECT LPAD('PostgreSQL', 15, '0') AS padded_string;
Output:
Example 2: Padding String to the left
To left-pad the string ‘PostgreSQL’ with (‘#’) to a total length of 15:
SELECT LPAD('PostgreSQL', 15, '#') AS padded_string;
Output:
Example 3: Left-Padding a Column
We will be using Zipcodes table.
Method 1: left padding of character column in postgresql:
Left pad of the character column in postgresql using LPAD() function.
select *,LPAD(city,10,'#') FROM zipcodes
City column is padded with ‘#’ on left side until the total string length becomes 10. So the resultant table will be
Output:
Method 2: left padding of numeric / integer column in PostgreSQL – Padding Zeros to the left:
Left pad of the numeric column in PostgreSQL using LPAD() function is done by typecasting numeric / integer column to character column.
select *,LPAD(zip::text,10,'0') as lpad_zip FROM zipcodes
padding zeros to the left of the Zip column until the total string length becomes 10. So, the resultant table will be
Output:
