Padding of the column in postgresql we use LPAD() function or RPAD() function. Left pad and right pad of column in postgresql is accomplished using LPAD() and RPAD() function. Let’s see how to
- Left pad of the column in postgresql using LPAD() function
- Right pad of the column in postgresql using RPAD() function
Syntax of LPAD AND RPAD():
We will be using Zipcodes table.
Left pad of the column in postgresql using LPAD() function:
Method 1: left pad 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
Method 2: left pad of numeric / integer column in postgresql:
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
Zip column is padded with ‘0’ on left side until the total string length becomes 10. So the resultant table will be
Right pad of the column in postgresql using RPAD() function:
Right pad of the character column in postgresql using RPAD() function.
select *,RPAD(city,10,'#') FROM zipcodes
City column is padded with ‘#’ on right side until the total string length becomes 10. So the resultant table will be