SQLProvider
SQLProvider copied to clipboard
Simple group by query with postgresql causes doubling up of double quotes when using double quoted declared fields
In PostGreSq with a table where the fields are declared double quotedl, I have the following query:
let! costs =
query {
for pol in ctx.Public.PurchaseOrderLine do
where (pol.JobId.Value = prodOrderID)
groupBy (pol.JobId, pol.ChargeAccount) into g
select
{
JobID = g.Key |> fst |> Option.defaultValue 0L
ChargeAccount = g.Key |> snd
TotalCost = g.Sum(fun o -> o.TotalCost)
}
}
|> Seq.executeQueryAsync
The following sql is emitted:
SELECT "pol"."JobID" as ""pol"."JobID"", "pol"."ChargeAccount" as ""pol"."ChargeAccount"", SUM("pol"."TotalCost") as "pol.SUM_TotalCost" FROM "public"."PurchaseOrderLine" as "pol" WHERE (("pol"."JobID" = @param1)) GROUP BY "pol"."JobID", "pol"."ChargeAccount">, Parameters<@param1=12482>
Note the as parts have been double quoted twice. This causes postgresql to return an error:
zero-length delimited identifier at or near """"
In fairness, groupby is discouraged in the docs.
The work arround obviously for me at present is to create a view or do the grouping in the client. But I would have thought this was a simple enough group by.