PostgreSQL translate() Function

The PostgreSQL translate() function translates a specified string according to the specified translation relation. A translation relation is a one-to-one correspondence between characters in two strings.

translate() Syntax

This is the syntax of the PostgreSQL translate() function:

translate(string, from_set, to_set)



Required. The string to translate.


Required. The string including characters to translate.


Required. The string that is the set of characters to translate to. The characters in from_set and the characters in to_set form a one-to-one translation relationship according the order of characters.

Return value

The PostgreSQL translate() function returns a translated string string with all characters specified in from_set be translated to the corresponding characters in to_set.

translate() Examples

This example shows how to use the translate() function to translate a string:

SELECT translate('xabcdef', 'abcd', '123');

Let’s take a look at the executing steps of translate('xabcdef', 'abcd', '123'):

  1. from_set is abcd. It tells us the four characters (a, b, c and d) are going to be translated.

  2. to_set is 123. The following translation relationships between from_set and to_set are established:

    • a will be translated into 1
    • b will be translated into 2
    • c will be translated into 3
    • d will be translated to '', that is the empty string
  3. The translation process is as follows:

    1. x is not in from_set, so x is reserved.
    2. a is in from_set, so a is translated into 1.
    3. b is in from_set, so b is translated into 2.
    4. c is in from_set, so c is translated into 3.
    5. d is in from_set, so d is translated into ''.
    6. e is not in from_set, so e is reserved.
    7. f is not in from_set, so f is reserved.
  4. The result of the translation was x123ef and translate() returned it.