使用PostgresJS驱动时Drizzle ORM无法将枚举转换为文本

我在PostgreSQL里有个eg_status枚举类型字段，写了下面这个查询函数，本来想用cast(${evergreen.eg_status} as text)把枚举转成文本后用ilike做模糊查询，结果运行时报错：

⨯ PostgresError: operator does not exist: egstatusenum ~~* unknown

我的函数代码如下：

export async function fetchFilteredEvergreen(
    query: string,
    currentPage: number
) {
    noStore();

    const data = db.select({
        'Host': evergreen.hostname,
        'Environment': evergreen.environment,
        'IT Service': evergreen.it_service,
        'IT Service Owner': evergreen.service_owner,
        'Tech Version': evergreen.software_product_version_name,
        'Intended Remediation': evergreen.intended_remediation_date,
        'Evergreen Status': evergreen.eg_status,
        'Helios': evergreen.helios,
        'Instance': evergreen.instance_name,
        'GBGF': evergreen.it_org_6,
        'IT Org 7': evergreen.it_org_7,
        'IT Org 8': evergreen.it_org_8,
    })
    .from(evergreen)
    .where(or(
        sql<string>`cast(${evergreen.eg_status} as text) ilike ${sql.placeholder('search')}`,
        ilike(evergreen.eg_status,sql.placeholder('search')),
        ilike(evergreen.helios,sql.placeholder('search')),
        ))
    .limit(sql.placeholder('limit'))
    .prepare('evg_stmt')

    const res = await data.execute({search: `%${query}%`,limit: currentPage})

    console.log(res);

    return res
}

问题原因 #

报错是因为你同时写了两个针对eg_status的查询条件，其中ilike(evergreen.eg_status, sql.placeholder('search'))这一行没有对枚举字段做类型转换，直接拿枚举类型和字符串做ilike比较，PostgreSQL没有对应的运算符，所以抛出错误。

解决方法 #

删掉那个没有做类型转换的ilike条件，只保留通过cast转成文本的查询语句就行，修正后的代码如下：

export async function fetchFilteredEvergreen(
    query: string,
    currentPage: number
) {
    noStore();

    const data = db.select({
        'Host': evergreen.hostname,
        'Environment': evergreen.environment,
        'IT Service': evergreen.it_service,
        'IT Service Owner': evergreen.service_owner,
        'Tech Version': evergreen.software_product_version_name,
        'Intended Remediation': evergreen.intended_remediation_date,
        'Evergreen Status': evergreen.eg_status,
        'Helios': evergreen.helios,
        'Instance': evergreen.instance_name,
        'GBGF': evergreen.it_org_6,
        'IT Org 7': evergreen.it_org_7,
        'IT Org 8': evergreen.it_org_8,
    })
    .from(evergreen)
    .where(or(
        sql<string>`cast(${evergreen.eg_status} as text) ilike ${sql.placeholder('search')}`,
        ilike(evergreen.helios, sql.placeholder('search')),
    ))
    .limit(sql.placeholder('limit'))
    .prepare('evg_stmt')

    const res = await data.execute({search: `%${query}%`, limit: currentPage})

    console.log(res);

    return res
}

补充说明 #

PostgreSQL的枚举类型不能直接和字符串进行ilike/like比较，必须先转换成文本类型。你原来的思路是对的，但多写了一个无效的条件导致报错，删掉后就能正常运行。

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