Confusion about PostGIS types and pgtype

[peacefixation]

I'm new to pgx and learning how to deal with PostGIS geometry types.

When I use lib/pq I can create a custom Geometry type in Go and implement sql.Scanner and driver.Valuer interfaces to read and write PostGIS geometry types.

type Geometry struct {
    geom.T
}

func (g *Geometry) Scan(value interface{}) error {
    // read the value and unmarshal to geom.T
    g.T = ...
    return nil
}

func (g *Geometry) Value() (driver.Value, error) {
    // marshal the geometry to WKB
    return value, nil
}

...

func SomeDBMethod() error {
    var g Geometry
    _ := db.QueryRow("SELECT ...", args).Scan(&g)
    // ...
    
    _, err := db.Exec("INSERT ...", g)
    // ...
}

I tried something similar with pgx and I get scan errors - pgx doesn't recognize the PostGIS geometry types.

After much reading I've discovered pgtype and also the go-geom and pgx-geom library. I have to call pgxgeom.Register() to let the pgx.Conn know about the PostGIS geometry types and I can now scan and value a geom.T.

I'm not sure why this is necessary, why can't I just implement sql.Scanner and driver.Valuer like I did with lib/pq?

Separately, if I do use go-geom and pgx-geom I'd like to wrap the geom.T type in a struct so I don't have to export go-geom types throughout the codebase - it's nice for the future if we change geometry libraries - but I don't think this is going to work unless I write my own code like pgx-geom to register my custom Go type with pgx.

Am I misunderstanding how and why to use pgtype?

[jackc]

I'm not sure why this is necessary, why can't I just implement sql.Scanner and driver.Valuer like I did with lib/pq?

You can. I'm not sure why your seeing errors.

I don't have PostGIS installed, but I created a simple test that shows it working with hstore, another extension type that isn't registered by default in pgx.

package main

import (
	"context"
	"database/sql/driver"
	"fmt"
	"log"
	"os"

	"github.com/jackc/pgx/v5"
)

type Hstore string

func (h *Hstore) Scan(src interface{}) error {
	fmt.Println("Hstore.Scan() called")
	*h = Hstore(src.(string))
	return nil
}

func (h Hstore) Value() (driver.Value, error) {
	fmt.Println("Hstore.Value() called")
	return string(h), nil
}

func main() {
	ctx := context.Background()

	conn, err := pgx.Connect(ctx, os.Getenv("DATABASE_URL"))
	if err != nil {
		log.Fatal(err)
	}
	defer conn.Close(ctx)

	var result Hstore

	err = conn.QueryRow(ctx, "select $1::hstore;", Hstore("foo => bar")).Scan(&result)
	if err != nil {
		log.Fatal(err)
	}

	log.Println(result)
}

jack@glados ~/dev/pgx_issues/pgx-2221 ±master⚡ » gor .
Hstore.Value() called
Hstore.Scan() called
2025/01/11 08:07:51 "foo"=>"bar"