PostgreSQL OVERLAY Function

Summary: in this tutorial, you’ll learn how to use the PostgreSQL OVERLAY function to replace a substring specified by a position and length with a new substring.

Introduction to the PostgreSQL OVERLAY function #

The OVERLAY function replaces a substring within a string with a new substring.

Unlike the REPLACE function, the OVERLAY function allows you to determine the starting position and length of the substring to be replaced.

Here’s the syntax of the OVERLAY function:

OVERLAY(string PLACING new_substring FROM start_position [FOR length])Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The OVERLAY function takes four parameters:

• string: the input string.
• new_substring: the new substring you use for replacement in the input string.
• start_position: the position in the original string where you want the replacement to begin. The start_positionis a 1-based index, meaning that the first character has a position of 1.
• length(optional) is the number of characters to replace in the input string. If you omit the length parameter, the function will use the length of the new_substring.

The OVERLAY function returns a new string with a substring specified by a starting position and length replaced by the new substring.

Basic PostgreSQL OVERLAY function examples #

The following statement uses the OVERLAY function to replace a substring in the string 'Hello World' starting from position 7:

SELECT
  OVERLAY(
    '123456789' PLACING 'abc'
    FROM
      4
  ) result;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

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

In this example:

• The FROM 4 returns a substring starting from position four.
• The length defaults to the new substring’s length ('abc'), which is 3.
• The function substitutes three characters in the input string with the substring 'abc', which returns '123abc789'.

If you want to replace only a specific number of characters, you can use the FOR clause:

SELECT
  OVERLAY(
    '123456789' PLACING 'abc'
    FROM
      4 FOR 5
  ) result;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

In this example:

• The FROM 4 FOR 5 returns the substring '45678'.
• The OVERLAY function replaces substring '45678' with the substring 'abc' .

Masking data #

The OVERLAY function can be helpful for data masking. For example, the following query uses the OVERLAY function to show only the last four digits of the home phones of the users:

SELECT
  first_name,
  home_phone,
  OVERLAY(
    home_phone PLACING '***'
    FROM 5
  ) masked_home_phone
FROM
  profiles;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 first_name |  home_phone  | masked_home_phone
------------+--------------+-------------------
 John       | 408-111-2222 | 408-***-2222
 Jane       | 408-111-3333 | 408-***-3333
 Alice      | 408-111-4444 | 408-***-4444
 Bob        | 408-111-5555 | 408-***-5555
 Charlie    | NULL         | NULLCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Edge cases #

Case 1: Negative starting position #

If the starting position is zero or negative, the OVERLAY function issues an error:

SELECT
  OVERLAY(
    '123456789' PLACING 'abc'
    FROM
      -1
  ) result;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Error:

ERROR: negative substring length not allowedCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Case 2: The starting position exceeds the length of the input string #

If the starting position exceeds the length of the input string, the OVERLAY function concatenates the input string with the substring:

SELECT
  OVERLAY(
    '123456789' PLACING 'abc'
    FROM
      10
  ) result;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

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

Case 3: Zero or negative length #

If the length is zero or negative, the OVERLAY function inserts the new substring at the starting position into the input string:

SELECT
  OVERLAY(
    '123456789' PLACING 'abc'
    FROM
      3 FOR 0
  ) result;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

    result
--------------
 12abc3456789
(1 row)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Case 4. NULL arguments #

The function returns NULL if any argument is NULL:

SELECT
  OVERLAY(
    '123456789' PLACING 'abc'
    FROM
      NULL FOR 0
  ) result;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 result
--------
 NULL
(1 row)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary #

• Use the OVERLAY function to replace a substring specified by a position and length with a new substring.