Error while inserting empty array using PGArray

[travitch]

I inadvertently inserted an empty array into a table and got an interesting error:

SqlError {sqlState = "42P18", sqlExecStatus = FatalError, sqlErrorMsg = "cannot determine type of empty array", sqlErrorDetail = "", sqlErrorHint = "Explicitly cast to the desired type, for example ARRAY[]::integer[]."}

Are there workarounds? And would it be possible to modify the ToField instance for PGArray to insert the appropriate cast (possibly only for empty arrays).

[lpsmith]

The standard workaround is to add a cast in your sql, e.g. [sql| ... ?::integer[] ... |]. It's not really possible to modify PGArray in any backwardly-compatible way. One could introduce a new constructor along the lines of the the Values constructor.

[travitch]

Oh, I didn't realize I could put a cast there. Thanks

[haskellGardener]

Sorry to add to this closed item, but when I attempt to use [sql| ... ?::text[] ...|] I get this message: "syntax error in multi-row template".

[travitch]

@haskellGardener I added a newtype wrapper around my type that had a custom ToField instance that added the cast. The function that parses queries in the sql quasiquoter doesn't like the cast syntax.

[lpsmith]

Yeah, the executeMany/returning syntax is very limited, unfortunately. It's remained unchanged from mysql-simple. In this case, the only real option at the moment is to create a newtype like @travitch suggested.

[haskellGardener]

Thanks. I thought I'd comment here to see if I had missed something.

Now that I looked at the source, I believe I could take a stab at creating an attoparsec parser that would allow for greater flexibility within the ?,?,?... pattern. Would that be of any interest? I don't want to impose yet another dependency on postgresql-simple like attoparsec if that would create problems for you.

[lpsmith]

postgresql-simple already depends on attoparsec, so that's no big deal.

My bigger concern here would be performance, as this is a computation that runs at run time, every time that executeMany runs. The existing parser is very simple, but I haven't really benchmarked it either. On the other hand, people have requested this sort of functionality before, and I would certainly entertain a patch. You might be interested in #67 and #20 for reference.

[haskellGardener]

I will have a go at it. Thanks for the pair of references. Performance should be taken into account as well, so we shall see if attoparsec is up to the task!