In PostgreSQL, we can use the overlay() function to replace a substring within a string. The function allows us to be very specific with regards to where in the string to start the operation and how much of it should be replaced.

Syntax

The syntax goes like this:

overlay ( string text PLACING newsubstring text FROM start integer [ FOR count integer ] ) 

The following examples demonstrate the various arguments.

Example

Here’s a basic example:

SELECT overlay('Cashflow' PLACING 'ino' FROM 4);

Result:

Casinoow

Here, we replaced part of the string Cashflow with the substring ino, starting from the fourth character.

If the FROM argument is greater than the length of the string, the substring is appended to the string:

SELECT overlay('Cashflow' PLACING 'ino' FROM 20);

Result:

Cashflowino

The FOR Argument

We have the option of using the FOR argument to specify how long to extend the replacement within the string. By default, this is the length of the new substring, but we can change this with the FOR argument.

We could therefore modify our first example like this:

SELECT overlay('Cashflow' PLACING 'ino' FROM 4 FOR 5);

Result:

Casino

To further demonstrate how this argument works, the following example shows how different values affect the outcome:

\x
SELECT 
    overlay('Cashflow' PLACING 'ino' FROM 4 FOR -4) AS "-4",
    overlay('Cashflow' PLACING 'ino' FROM 4 FOR -3) AS "-3",
    overlay('Cashflow' PLACING 'ino' FROM 4 FOR -2) AS "-2",
    overlay('Cashflow' PLACING 'ino' FROM 4 FOR -1) AS "-1",
    overlay('Cashflow' PLACING 'ino' FROM 4 FOR 0) AS "0",
    overlay('Cashflow' PLACING 'ino' FROM 4 FOR 1) AS "1",
    overlay('Cashflow' PLACING 'ino' FROM 4 FOR 2) AS "2",
    overlay('Cashflow' PLACING 'ino' FROM 4 FOR 3) AS "3",
    overlay('Cashflow' PLACING 'ino' FROM 4 FOR 4) AS "4",
    overlay('Cashflow' PLACING 'ino' FROM 4 FOR 5) AS "5",
    overlay('Cashflow' PLACING 'ino' FROM 4 FOR 6) AS "6";

Result:

-4 | CasinoCashflow
-3 | CasinoCashflow
-2 | Casinoashflow
-1 | Casinoshflow
0  | Casinohflow
1  | Casinoflow
2  | Casinolow
3  | Casinoow
4  | Casinow
5  | Casino
6  | Casino

Using a Negative FROM Argument

We can see from the previous example that we can use a negative value for the FOR argument. But if we use a negative value for the FROM argument, it’s a different story:

SELECT overlay('Cashflow' PLACING 'ino' FROM -4);

Result:

ERROR:  negative substring length not allowed

We get the same error when using zero:

SELECT overlay('Cashflow' PLACING 'ino' FROM 0);

Result:

ERROR:  negative substring length not allowed

When the New String is Empty

If the replacement string is empty, then the original string is returned unchanged:

SELECT overlay('Cashflow' PLACING '' FROM 4);

Result:

Cashflow

When the Original String is Empty

If the original string is empty, then the new string is returned:

SELECT overlay('' PLACING 'ino' FROM 4);

Result:

ino

When Both Original String and New String are Empty

Unsurprisingly, if both the new string and the original string are empty, then an empty string is returned:

SELECT overlay('' PLACING '' FROM 4);

Result:

 overlay 
---------