Right pad of column in PostgreSQL – RPAD()

In PostgreSQL, to right pad a column values we will be using the RPAD() function. This function pads the right side of a string with a specified character up to a specified length.

Let’s see how to

• Right pad of the column in PostgreSQL using RPAD() function
• Padding Zeros to the right of the column in PostgreSQL
• Right Padding Zeros or String to Numeric column in PostgreSQL table

Syntax of RPAD() :

• 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 right

To right-pad the string ‘PostgreSQL’ with zeros (‘0’) to a total length of 15:

 

SELECT RPAD('PostgreSQL', 15, '0') AS padded_string;

Output:

Example 2: Padding String to the right

To right-pad the string ‘PostgreSQL’ with (‘#’) to a total length of 15:

 

SELECT RPAD('PostgreSQL', 15, '#') AS padded_string;

Output:

Example 3: Right-Padding a Column

We will be using Zipcodes table.

Method 1: right padding of character column in PostgreSQL:

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

Output:

Method 2: right padding of numeric / integer column in PostgreSQL

Right pad of the numeric column in PostgreSQL using RPAD() function is done by typecasting numeric / integer column to character column.

 

select *,RPAD(zip::text,10,'0') as rpad_zip FROM zipcodes

padding zeros to the right of the Zip column until the total string length becomes 10. So, the resultant table will be

Output: