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.fiber

FROM 

    on_plan_buy pbuy

INNER JOIN

      card_fiche fiche

      ON pbuy.uuid = fiche.ad_uuid

INNER JOIN

     on_plan_seller seller

     ON pbuy.seller_id = seller.id

INNER JOIN

        myconstants conste

        ON true

WHERE 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 END

LIMIT 100;

但是当我在golang中这样做时：

query = `

WITH myconstants (nb_pieces,nb_room,is_fiber) as (

    values ($3,$4,$5)

 )

SELECT 

    pbuy.name

FROM 

    ` + tableOnPlan + ` pbuy

INNER JOIN

    card_fiche fiche

    ON pbuy.uuid = fiche.ad_uuid

INNER JOIN

    myconstants conste

    ON true

WHERE 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 END

LIMIT 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 END

fmt.Println(query)

err = db.Select(&response.Offers, query, gsm.PriceMax, gsm.PriceMin, gsm.NbRoom, gsm.NbChamber, gsm.Fiber)

Answer 1

我猜PostgreSQL不知道CTE中的列是什么类型。当你在这样说的时候：pg_adminWITH myconstants (nb_pieces,nb_room,is_fiber) as (&nbsp; &nbsp; values (0,0,true))每个人都知道这是一个布尔值，所以一切都有效。但是，当您在 Go 中使用它时：trueWITH myconstants (nb_pieces,nb_room,is_fiber) as (&nbsp; &nbsp; values ($3,$4,$5))驱动程序可能不知道要使用什么类型，因此它具有类型文本，并最终尝试与 进行比较。text$5conste.is_fiberconste.is_fiber = falsetextboolean你应该能够通过一些投射来解决这个问题：WITH myconstants (nb_pieces, nb_room, is_fiber) as (&nbsp; &nbsp; values ($3::int, $4::int, $5::boolean))