PSQL 运算符在 postgresql 中不存在

Question

这是我的原始查询，它适用于针对同一数据库的pg_admin：WITH myconstants (nb_pieces,nb_room,is_fiber) as (        values (0,0,true) )SELECT     pbuy.name, seller.name as seller_name, fiche.fiberFROM     on_plan_buy pbuyINNER JOIN      card_fiche fiche      ON pbuy.uuid = fiche.ad_uuidINNER JOIN     on_plan_seller seller     ON pbuy.seller_id = seller.idINNER JOIN        myconstants conste        ON trueWHERE pbuy.code_insee IN ('92108','75018')        AND pbuy.price <= 9999999 AND pbuy.price >= 0        AND CASE WHEN conste.is_fiber = false THEN true ELSE fiche.fiber = true ENDLIMIT 100;但是当我在golang中这样做时：query = `WITH myconstants (nb_pieces,nb_room,is_fiber) as (    values ($3,$4,$5) )SELECT     pbuy.nameFROM     ` + tableOnPlan + ` pbuyINNER JOIN    card_fiche fiche    ON pbuy.uuid = fiche.ad_uuidINNER JOIN    myconstants conste    ON trueWHERE pbuy.code_insee IN ('` + strings.Join(gsm.CodeInsee, "','") + `')    AND pbuy.price <= $1 AND pbuy.price >= $2    AND CASE WHEN conste.is_fiber = false THEN true ELSE fiche.fiber = true ENDLIMIT 100;`//AND CASE WHEN conste.nb_pieces = 0 THEN pbuy.piece > 0 ELSE pbuy.piece = conste.nb_pieces END//AND CASE WHEN conste.nb_room = 0 THEN pbuy.chambre > 0 ELSE pbuy.chambre = conste.nb_room END//AND CASE WHEN conste.is_fiber = false THEN true ELSE fiche.fiber = true ENDfmt.Println(query)err = db.Select(&response.Offers, query, gsm.PriceMax, gsm.PriceMin, gsm.NbRoom, gsm.NbChamber, gsm.Fiber)

Answer 1

我猜PostgreSQL不知道CTE中的列是什么类型。当你在这样说的时候：pg_admin

WITH myconstants (nb_pieces,nb_room,is_fiber) as (

    values (0,0,true)

)

每个人都知道这是一个布尔值，所以一切都有效。但是，当您在 Go 中使用它时：true

WITH myconstants (nb_pieces,nb_room,is_fiber) as (

    values ($3,$4,$5)

)

驱动程序可能不知道要使用什么类型，因此它具有类型文本，并最终尝试与 进行比较。text$5conste.is_fiberconste.is_fiber = falsetextboolean

你应该能够通过一些投射来解决这个问题：

WITH myconstants (nb_pieces, nb_room, is_fiber) as (

    values ($3::int, $4::int, $5::boolean)

)