Understanding RTRIM() in PostgreSQL

In PostgreSQL, we can use the rtrim() function to trim the right side of a string. We can trim blank spaces or we can specify certain characters to trim.

It removes the longest string containing only characters from the ones we specify. If we don’t specify any characters, then it removes blank spaces.

Trimming Blank Space

Here’s a quick example to demonstrate how to trim blank space from the right side of a string:

SELECT rtrim('     Dog     ');

Result:

  rtrim   
----------
Dog

We can see that it trimmed the right side of the string but not the left. I didn’t specify any characters to trim and so only white space was trimmed.

Trimming Other Characters

We can trim other characters from the string by including them as a second argument:

SELECT rtrim('pad-Dog-pad', '-pad');

Result:

  rtrim  
---------
pad-Dog

It doesn’t require that all specified characters are in the string, just that one of them is at the start of the string at any point in the trim operation.

Therefore, we’ll still get the same result if we change the order of the characters in the second argument, and even if we add more characters:

SELECT rtrim('pad-Dog-pad', '-dapzy');

Result:

  rtrim  
---------
pad-Dog

Here’s what happens if we remove the a character from our list:

SELECT rtrim('pad-Dog-pad', '-dpzy');

Result:

   rtrim    
------------
pad-Dog-pa

This time it stopped trimming as soon as it got to the a character, because that wasn’t in the list of characters to trim.

We can include the blank space in our list of characters to trim if required:

SELECT rtrim('Dog pad', ' pad');

Result:

 rtrim 
-------
Dog

In fact, if we don’t do this, and there are any blank spaces at the end of the string, then nothing would get trimmed.

Here’s an example of what I mean:

SELECT rtrim('Dog pad ', 'pad');

Result:

  rtrim   
----------
Dog pad

The string is returned untrimmed because I removed the blank space from the list of characters to trim. Given there was white space at the end of the string, nothing was trimmed.

Passing Null Arguments

If any of the arguments are null, then null is returned:

SELECT 
    rtrim(null, ' pad') AS "1",
    rtrim('Dog ', null) AS "2";

Result:

  1   |  2   
------+------
null | null