v.24.10Improvement

Allow empty needle in replace function for PostgreSQL compatibility

Official CH changelog text
Allow empty needle in the function replace, the same behavior with PostgreSQL. #69918 (zhanglistar).
Allow the replace function to accept an empty needle string, aligning its behavior with PostgreSQL.

Why it matters

This feature solves the inconsistency between ClickHouse and PostgreSQL where passing an empty string as the needle to the replace function was previously unsupported or behaved differently. By allowing an empty needle, it improves compatibility and predictability for users migrating queries or working across both systems.

How to use it

Simply use the replace function with an empty string as the needle argument. For example:

SELECT replace('clickhouse', '', 'x')

This will now work as expected, similar to PostgreSQL's behavior.

Related resources

PreviousNext