regexp_replace() vs. replace() to replace all matches in a string

By default, in PostgreSQL, regexp_replace() replaces the first occurrence of a substring in a string, while replace() replaces all occurrences of a substring in a string. Why is the default behavior different?

(I know that with regexp_replace one can use the g option to replace all occurrences of a substring in a string.)

Examples:

SELECT regexp_replace('hello world', 'o', 'z');  -- returns "hellz world"
SELECT regexp_replace('hello world', 'o', 'z', 'g'); -- returns "hellz wzrld"
SELECT replace('hello world', 'o', 'z'); -- returns: "hellz wzrld"

Why?

Because Postgres replace() is a standard SQL function that works the same as in other RDBMS. Example: replace() in SQL Server:


Replaces all occurrences of a specified string value with another string value.

While regexp_replace() is used to ...


Replace substring(s) matching a POSIX regular expression.

The handling of regular expressions is obviously guided by the POSIX standard and works the same as other tools implementing it. The manual:


POSIX regular expressions provide a more powerful means for pattern
matching than the LIKE and SIMILAR TO operators. Many Unix tools such
as egrep, sed, or awk use a pattern matching language that is similar
to the one described here.

StackExchange Database Administrators Q#134142, answer score: 6