Left pad and right pad of column in Postgresql

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():

LPAD(column_name,len,[fill_text])
RPAD(column_name,len,[fill_text])

We will be using Zipcodes table.

Left pad and right pad of column in Postgresql 1

 

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

Left pad and right pad of column in Postgresql 2

 

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

Left pad and right pad of column in Postgresql 3

 

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

Left pad and right pad of column in Postgresql 4