PostgreSQL转换为UUID时如何指定类型转换报错的处理行为

最优解决方案 #

如果你使用的是 PostgreSQL 16及以上版本，直接使用原生支持的TRY_CAST函数即可，该函数在类型转换失败时会自动返回NULL，不会抛出语法错误，完全符合左连接的匹配逻辑，非法UUID的内容会自然关联不上，不需要额外写校验规则：

LEFT JOIN table2 ON table1.user_uuid = TRY_CAST(regexp_replace(table2.url, '.*[/](.*)$', '\1') AS uuid)

低版本兼容方案 #

如果使用的是PostgreSQL 13~15版本，可以用内置的UUID合法性校验函数pg_uuid_is_valid实现同等效果，只需要一行判断逻辑，不需要复杂正则校验：

LEFT JOIN table2 ON table1.user_uuid = CASE 
  WHEN pg_uuid_is_valid(regexp_replace(table2.url, '.*[/](.*)$', '\1')) 
  THEN regexp_replace(table2.url, '.*[/](.*)$', '\1')::uuid 
END

方案优势 #

相比把UUID转成文本匹配的临时方案，上述实现可以利用table1.user_uuid的原生UUID类型索引，不会出现隐式类型转换导致的索引失效问题，查询性能更好。

内容的提问来源于stack exchange，提问作者user16966858