pgtypes v5 support through standard library driver API

[killzoner]

Describe the bug
A clear and concise description of what the bug is.

Hi,
I was previously using v4 and pgtype.TextArray through standard driver connection (cf https://github.com/jackc/pgx/wiki/Getting-started-with-pgx-through-database-sql).
When transitionning to v5 I tested pgtype.FlatArray[T] and pgtype.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 (ie sql.Open function). (1)
I was previously using a wrapper through sqlx to have the standard library connection, but it seems that it's not working either through pure dbscan connection (stdlib version of scany) (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 to pgx 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 some go 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 through dbscan)
3) : https://github.com/killzoner/embedded-postgres/blob/d9a7713e62804c06328b12bdacab675d7b236016/examples/examples_test.go#L146 (pgx native interface and scany)

Main focus is on 1)

Expected behavior

Struct scanning works as expected with stdlib driver.

Actual behavior

Struct scanning is working with v4 but not v5

Version

• Go: $ go version -> go version go1.19.4 linux/amd64
• PostgreSQL: default on tests is 12.1.0 but i'm using postgres:14-alpine locally to test through Docker.
• pgx: $ 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

[jackc]

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... ☹️

[killzoner]

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

[elvizlai]

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)

[jackc]

@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)
	}
}

[vtolstov]

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[]

[jackc]

Candidate solution: #2020