Open killzoner opened 1 year ago
I think this is an unintended side-effect of the new Codec
system as well as moving to generics for array support. In v4, each array type was made through code generation. This allowed the parsing to be hard coded per type.
In v5, the primary parsing interface is the PlanScan
method of the Codec
interface. This method requires a pgtype.Map
and the PostgreSQL OID of the type. Neither of these are available in the sql.Scanner
interface.
The solution pgx provides is https://pkg.go.dev/github.com/jackc/pgx/v5@v5.2.0/pgtype#Map.SQLScanner. The works when using database/sql and pgx/stdlib directly. But it did not anticipate using something like sqlx where the underlying type must be used directly. ☹️
I'm not sure what the best solution is. How did lib/pq's array support work with sqlx. I think they used some sort of wrapper was well.
We may end up needing concrete types for stdlib apart from pgtype... ☹️
Hey @jackc sorry for the late answer.
At least it points that i was in the right direction and some piece is missing. From what i remember and my first tests, lib/pq
seems to be still working through pgx
v5, but i'm not really aware of the magic used for that.
Happy to work on any complementary stuff to fill this gap if you can point me to something
any progress about this issue?
For string slice insert using stdlib, we need using pq.Array as wrapper, but no suitable pgtype found.(Scan can be resolved using pgtypeMap)
@elvizlai
I think you might have a different issue. []string
insert with stdlib should work already.
package main
import (
"database/sql"
"fmt"
"log"
"os"
_ "github.com/jackc/pgx/v5/stdlib"
)
func main() {
db, err := sql.Open("pgx", os.Getenv("DATABASE_URL"))
if err != nil {
log.Fatal(err)
}
defer db.Close()
names := []string{"jack", "jill", "joe"}
rows, err := db.Query("select * from unnest($1::text[])", names)
if err != nil {
log.Fatal(err)
}
for rows.Next() {
var n string
err = rows.Scan(&n)
if err != nil {
log.Fatal(err)
}
fmt.Println(n)
}
err = rows.Err()
if err != nil {
log.Fatal(err)
}
}
how to deal with text[] type in postgres in go via sqlx ? i'm try []string []string []pgtype.Text and pgtype.Array[pgtype.Text] and nothing works
but error always like unsupported Scan, storing driver.Value type string into type *[]string or somthing like that so why pgx thinks that in postgres i have string type? my column definition have text[]
Candidate solution: https://github.com/jackc/pgx/pull/2020
Describe the bug A clear and concise description of what the bug is.
Hi, I was previously using
v4
andpgtype.TextArray
through standard driver connection (cf https://github.com/jackc/pgx/wiki/Getting-started-with-pgx-through-database-sql). When transitionning tov5
I testedpgtype.FlatArray[T]
andpgtype.Array[T]
, but the driver does not seem to be able to translate these new types using struct scanning.It seems to work through
scany
using "pure"pgx
connection (3), but not using standard library driver (iesql.Open
function). (1) I was previously using a wrapper throughsqlx
to have the standard library connection, but it seems that it's not working either through puredbscan
connection (stdlib
version ofscany
) (2)Strange thing also, I seem to be able to do inserts (writes) in database correctly but not struct scan (so reads). Also noticed that very few types are exposed through
stdlib
compared topgx
native interface (https://github.com/jackc/pgx/blob/master/stdlib/sql.go#L90 vs https://github.com/jackc/pgx/blob/master/pgtype/pgtype.go#L211) but maybe this is normal.To Reproduce Steps to reproduce the behavior:
Minimal repo is in this repository :
https://github.com/killzoner/embedded-postgres/tree/d9a7713e62804c06328b12bdacab675d7b236016/examples
(you should be able to do somego test
in the folder).The cases specified below are there: 1): https://github.com/killzoner/embedded-postgres/blob/d9a7713e62804c06328b12bdacab675d7b236016/examples/examples_test.go#L80 (standard driver through
sqlx
) 2): https://github.com/killzoner/embedded-postgres/blob/d9a7713e62804c06328b12bdacab675d7b236016/examples/examples_test.go#L113 (standard driver throughdbscan
) 3) : https://github.com/killzoner/embedded-postgres/blob/d9a7713e62804c06328b12bdacab675d7b236016/examples/examples_test.go#L146 (pgx
native interface andscany
)Main focus is on 1)
Expected behavior
Struct scanning works as expected with
stdlib
driver.Actual behavior
Struct scanning is working with
v4
but notv5
Version
$ go version
->go version go1.19.4 linux/amd64
12.1.0
but i'm usingpostgres:14-alpine
locally to test through Docker.$ grep 'github.com/jackc/pgx/v[0-9]' go.mod
->github.com/jackc/pgx/v5 v5.2.0
Additional context Not sure if struct scanning was supposed to work in the first place