If you’re getting an error that reads “negative substring length not allowed” when using PostgreSQL’s overlay() function, it’s probably because you’re passing a negative value (or zero) to the FROM argument.

To fix this issue, be sure that the FROM argument is a positive integer.

Example of Error

Here’s an example of code that produces the error:

SELECT overlay('Th...and' PLACING 'ail' FROM -3);

Result:

ERROR:  negative substring length not allowed

We get the same error when using zero:

SELECT overlay('Th...and' PLACING 'ail' FROM 0);

Result:

ERROR:  negative substring length not allowed

Solution

To fix this error, change the FROM argument to a positive integer:

SELECT overlay('Th...and' PLACING 'ail' FROM 3);

Result:

Thailand

It’s possible for the argument to be greater than the actual length of the string. In such cases, the substring is appended to the string.

For example:

SELECT overlay('Th...and' PLACING 'ail' FROM 30);

Result:

Th...andail