PostgreSQL RTRIM() Function

Summary: in this tutorial, you will learn how to use the PostgreSQL RTRIM() function to remove specified characters from the end of a string.

Introduction to PostgreSQL RTRIM() function

The RTRIM() function allows you to remove specified characters from the end of a string.

Here’s the syntax of the RTRIM() function:

RTRIM(string, character)Code language: SQL (Structured Query Language) (sql)

In this syntax:

• string is the input string that you want to remove characters
• character specifies the character you want to remove from the end of the string. The character parameter is optional and defaults to space.

The RTRIM() function returns the string with all trailing characters removed.

To remove both leading and trailing characters from a string, you use the TRIM() function.

To remove all the leading characters from a string, you use the LTRIM() function.

PostgreSQL RTRIM() function examples

Let’s explore some examples of using the RTRIM() function.

1) Basic PostgreSQL RTRIM() function example

The following example uses the RTRIM() function to remove the character ! from the end of the string postgres!!!:

SELECT RTRIM('postgres!!!', '!');Code language: SQL (Structured Query Language) (sql)

Output:

  rtrim
----------
 postgres
(1 row)Code language: SQL (Structured Query Language) (sql)

2) Using the PostgreSQL RTRIM() function to remove leading spaces

The following example uses the RTRIM() function to remove all the spaces from the end of the string 'PostgreSQL ':

SELECT RTRIM('PostgreSQL   ');Code language: SQL (Structured Query Language) (sql)

Output:

   rtrim
------------
 PostgreSQL
(1 row)
Code language: SQL (Structured Query Language) (sql)

Because the default of the second argument of the RTRIM() function is space, you don’t need to explicitly specify it.

3) Using the RTRIM() function with table data example

First, create a new table called tweets and insert some rows into it:

CREATE TABLE tweets(
   id SERIAL PRIMARY KEY,
   tweet VARCHAR(120) NOT NULL
);

INSERT INTO tweets(tweet)
VALUES
   ('PostgreSQL tutorial   '),
   ('PostgreSQL RTRIM() function   ')
RETURNING *;Code language: SQL (Structured Query Language) (sql)

Output:

 id |             tweet
----+--------------------------------
  1 | PostgreSQL tutorial
  2 | PostgreSQL RTRIM() function
(2 rows)


INSERT 0 2Code language: SQL (Structured Query Language) (sql)

Second, update the tweets by removing the trailing spaces using the RTRIM() function:

UPDATE tweets
SET tweet = RTRIM(tweet);Code language: SQL (Structured Query Language) (sql)

Output:

UPDATE 2Code language: SQL (Structured Query Language) (sql)

The output indicates that two rows were updated.

Third, verify the updates:

SELECT * FROM tweets;Code language: SQL (Structured Query Language) (sql)

Output:

 id |            tweet
----+-----------------------------
  1 | PostgreSQL tutorial
  2 | PostgreSQL RTRIM() function
(2 rows)Code language: SQL (Structured Query Language) (sql)

Summary

• Use RTRIM() function to remove all specified characters from the end of a string.