PostgreSQL枚举字段用COALESCE替换NULL为空字符串报错解决

你用COALESCE时踩了PostgreSQL枚举类型的特性：COALESCE会自动以第一个参数的类型作为返回值类型。你的date_unit是枚举类型，仅接受'day'和'month'，当你写coalesce(date_unit, '')时，数据库会尝试把空字符串''转换成该枚举类型，但''不在枚举的有效值列表里，因此直接触发类型转换错误。

方法1：先转字符串再替换NULL（推荐） #

先把枚举字段转换成text类型，再用COALESCE替换NULL为空字符串，此时返回值为字符串类型，不会触发枚举类型校验：

SELECT id,
    user_id,
    coalesce(date_unit::text, '') date_unit
FROM table_name
WHERE user_id = $1

对应Golang SQLX的结构体，需要把DateUnit字段的类型改为string（而非自定义枚举类型）：

type TableRecord struct {
    ID       int    `db:"id"`
    UserID   int    `db:"user_id"`
    DateUnit string `db:"date_unit"`
}

方法2：扩展枚举类型（不推荐） #

如果业务必须让枚举字段支持空字符串作为有效值，可以修改枚举类型添加该值：

ALTER TYPE table_name.date_unit ADD VALUE '';

但这种方法会改变枚举的定义，可能影响其他依赖该枚举的业务逻辑，非必要不建议使用。

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