PostgreSQL offers many functions that are used to manipulate data. The OVERLAY() function is one of them. OVERLAY() function in Postgres replaces a specified number of characters in a main string with another specified string. This blog covers how the OVERLAY() function works. Let's get started!

How Does PostgreSQL Overlay() Function Work?

The OVERLAY() function in Postgres replaces some specified characters in the given main string with another specified string. The information about the position of the replacement string is also provided in the query. The basic syntax for the OVERLAY() function is:

SELECT OVERLAY(<the_main_str> PLACING <the_replacing_str>  FROM <start_position> [ FOR<number_of_char>] );

So in the above syntax, in the OVERLAY function:

• The first thing we specify is the main string. It is the string in which the characters will be replaced.
• Secondly, after using the PLACING keyword we will specify the string that is the replacement.
• After the FROM statement, we will specify the position of characters in the main string from where the string will be replaced.
• After the FOR keyword, we will write the number of characters that are to be replaced from the main string.

The above syntax will be more clear if we understand it using an example.

Example

First, let’s see a simple example. Consider the main string as ‘Peter lives in England’. Now if we want to replace “Peter” with “Sarah” we will write the following query:

SELECT OVERLAY('Peter lives in England' PLACING 'Sarah' FROM 1 FOR 5 );

Now let’s have a glance at how we write the above query. The description for the queries is given below:

The string “Peter” will be replaced by “Sarah” as the replacement starts from position 1 in the main string till the next 5 characters.

The output for the query is as expected:

For another example, let’s consider the string 'This is an example of PostgreSQL function' In this example if we want to replace the “PostgreSQL” with “overlay”, we need to consider the following query:

SELECT OVERLAY('This is an example of PostgreSQL function' PLACING 'overlay' FROM 23 FOR 10 );

Now see the output of the function:

We want to replace ”PostgreSQL” which starts from the 23rd position so we will write FROM 23. And the whole word “PostgreSQL” contains 10 characters that are to be replaced, this is the reason we wrote 10 after the FOR keyword.

So this was all about the OVERLAY() function. We have learned how the overlay function works in detail.

Conclusion

The OVERLAY() function in PostgreSQL replaces a specific string with some other string which is also specified in the query. The position and character to be replaced by the string are also specified in the query. This post has demonstrated the use of the OVERLAY() function in PostgreSQL.