PostgreSQL RTRIM Function

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

Introduction to PostgreSQL RTRIM() function #

The RTRIM() function removes specified characters from the end of a string.

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

RTRIM(string [, characters])Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The RTRIM() function takes two parameters:

• string the input string you want to remove characters.
• characters is the characters you want to remove from the end of the input string.

The RTRIM() function returns a new string with the characters removed from the end of the input string. It returns NULL if the string and/or characters is NULL.

Basic PostgreSQL RTRIM function examples #

The following statement uses the RTRIM() function to remove the character (.) from a string:

SELECT
  RTRIM('Postgres...', '.') result;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

  result
----------
 PostgresCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The following query uses the RTRIM() function to remove trailing spaces from a string:

SELECT
  RTRIM('pgtutorial.com  ', '') result;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

     result
-----------------
  pgtutorial.comCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

It’s equivalent to the following query that uses the RTRIM() function without a space as the second parameter:

SELECT
  RTRIM('pgtutorial.com   ') result;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

     result
----------------
 pgtutorial.comCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Using RTRIM with table data #

First, create a new table called tasks to store task list:

CREATE TABLE IF NOT EXISTS tasks (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  task VARCHAR(255) NOT NULL
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Next, insert some rows into the tasks table:

INSERT INTO
  tasks (task)
VALUES
  ('Perform stock checking.'),
  ('Compare book and physical stocks.'),
  ('Adjust book stock...');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Then, remove the dot (.) from the end of the tasks when retrieving data from the tasks table:

SELECT
  id,
  RTRIM(task, '.') task
FROM
  tasks
ORDER BY
  id;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 id |               task
----+----------------------------------
  1 | Perform stock checking
  2 | Compare book and physical stocks
  3 | Adjust book stockCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary #

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