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.