`coalesceDefault [arrayAgg x] (val [])` causes a malformed array literal

[isomorpheme]

I'm writing a query using arrayAgg, which can return a NULL/Nothing value, so I'm combining it with coalesceDefault to just get an empty array in that case. E.g.:

query =
  select $ do
    x <- from $ values [val (1 :: Int), val 2, val 3]

    return $ coalesceDefault [arrayAgg x] (val [])

But executing this throws an exception saying the query has a malformed array literal:

*** Exception: SqlError {sqlState = "22P02", sqlExecStatus = FatalError, sqlErrorMsg = "malformed array literal: \"[]\"", sqlErrorDetail = "\"[\" must introduce explicitly-specified array dimensions.", sqlErrorHint = ""}

On the other hand, if I omit the coalesce:

query =
  select $ do
    x <- from $ values [val (1 :: Int), val 2, val 3]

    return $ arrayAgg x

Then I get the expected result of [Value {unValue = Just [1,2,3]}]

My guess is that the error happens because the faulty query uses the PersistField [a] instance, which serialises the literal as a PersistList instead of a PersistArray. Perhaps arrayAgg shouldn't return a plain list but use the (currently private) PostgresArray from persistent-postgresql.

[parsonsmatt]

Yeah, this is a really unfortunate issue with the PersistField [a] instance. If it were up to me, I'd abandon it entirely, and make everyone specify a newtype that picks how the database is supposed to render it. It doesn't help that the current implementation of PersistField [a] is a pretty bad choice to begin with.

[rinn7e]

Hi everyone, I'm having the same issue. Is there any workaround at the moment? (using unsafe function or write custom types)

[rinn7e]

I found a workaround, we can use unsafeSqlValue

mkPostgresArray :: Show a => [a] -> SqlExpr (Value b) 
mkPostgresArray list =
  let rawValue = "'{" <> Text.intercalate ", " ((Text.pack . show) <$> list) <> "}'"
  in unsafeSqlValue $ TLB.fromText rawValue
  
query bIds =
  			...
            having (
              (arrayAggWith AggModeDistinct
                (a ^. ABId) 
                [asc (a ^. ABId)]) 
                ==. mkPostgresArray (List.sort bIds)
              )

[parsonsmatt]

You almost certainly don't want Show there - PersistField is safer, and you'll want to use val to properly put them into the list. But then, yes, from there, you can synthesize the raw value from that.

You may need to handle the empty list case separately, as well