Question: pgx v5 now supports scan to struct. Are there any differences between scany and it?

[ccrotty]

Pretty much the title question. I have used scany for a while with pgx v4 and am now updating to pgx v5. With pgx v5 supporting scan to struct with RowToStruct..., are there any differences between using scany or the pgx libs? Looked online, but couldn't find anything comparing the two.

Thanks, for the time and thanks for the great library!

[sourcec0de]

Doesn't appear to be the case.
Just dropped scany in favor of pgx5 libs.
Great library if you're stuck on pgx4 for some reason.

pgx.CollectRows(rows, pgx.RowToStructByName[*MyType])

[georgysavva]

@ccrotty I honestly haven't had a chance to investigate the capabilities of pgx v5 and its structure scanning yet

[zolstein]

I've recently done some work with the struct scanning in pgx and scany (though I'm more familiar with pgx than scany). So hopefully I can comment usefully.

pgx can map from SQL columns to struct fields by field-name, tag, or position in the struct, whereas I think scany only supports field-name and tag. pgx's name mapping is also, I think, a bit more flexible than scany in how it handles non-ASCII characters. pgx also handles nested structs and pointers to structs substantially differently from scany - if you have code using scany to populate fields of nested structs, swapping to pgx might just work, but I wouldn't assume it will by default.

pgx's mechanism for scanning into structs is also (as of now) substantially slower than scany, and does much more allocation, which puts more pressure on the GC. I actually created a drop-in replacement library to pgx's collect functions to address some of these issues. It's probably not directly relevant to you since you could just keep using scany rather than pull in a new library, but it may be useful to compare against pgx and see what potential improvements exist. (Also, scany does better in part because I was able to port some of the improvements from my library.)

[georgysavva]

@zolstein, thank you for your detailed review. I hope it was helpful for the people in this thread. I can only add that scany also supports the generic database/sql interface apart from the pgx's native one. It means you can use scany with other postgres database driver libraries other than pgx and other relational databases.

[smlx]

pgx v5 still omits the single call Select and Get functions, but it looks like they are coming in a future version, and with the new pgx API it is quite simple to write your own implementation.