postgresql - `select regexp_replace(, '\s+ ', ' ')` 想要显式类型转换，但是怎么做？

Question

查询:

update enquiry_address_data  
set address_line_1 = upper(regexp_replace(btrim(address_line_1), '\s+', ' '));

返回

ERROR: function regexp_replace(text, "unknown", "unknown") does not exist
Hint: No function matches the given name and argument types. You may need to add explicit type casts.

我像这样添加了显式类型转换:

update enquiry_address_data  
set address_line_1 = upper(regexp_replace(btrim(address_line_1), '\s+' :: text, ' ' :: text));

我还是明白了

ERROR: function regexp_replace(text, character varying, character varying) does not exist
Hint: No function matches the given name and argument types. You may need to add explicit type casts.

我正在使用连接到 Amazon RedShift 实例的 PostgreSql 9.3 JDBC 驱动程序

Answer 1

Redshift 没有 regexp_replace，它是旧 PostgreSQL 版本的分支，添加了一堆东西。

请参阅 Redshift 文档。另请参阅 \df pg_catalog.* 或 \df pg_catalog.*regexp*。