PostGIS geography type question

[dankobg]

I have a geography column and i want to use a custom type for that.

I tested using these 2 libs:

• github.com/twpayne/pgx-geom
• github.com/paulmach/orb

They implement geo types as well as ewkb encoding and work with Scanner and Valuer interface.

Problem is that i can't use that type directly when scanning or passing as param for insert.
Pgxv5 uses binary protocol by default and those libs also implement PreferredFormat interface with binary format.
Scanning fails unless i manually cast it to ::bytea or ST_AsBinary. Same for insert, i have to cast as ::bytea or use ST_GeomFromEWKB.

Not sure what is happening, is this intended behavior or not? I expected binary format to be default and assumed.
These technically work but every library using pgxv5 that works with structs will fail and i have to convert every query which is bit annoying.

i have this table

    Column   |           Type           | Collation | Nullable |             Default
 ------------+--------------------------+-----------+----------+----------------------------------
  id         | bigint                   |           | not null | generated by default as identity
  query      | text                     |           | not null |
  coords     | geography(Point,4326)    |           | not null |
 Indexes:
     "pk_geocoding_result_id" PRIMARY KEY, btree (id)

Example 1 using github.com/twpayne/pgx-geom

func TestInsertPgxGeom(ctx context.Context, pool *pgxpool.Pool) {
	coords := &ewkb.Point{Point: geom.NewPoint(geom.XY).SetSRID(4326).MustSetCoords(geom.Coord{45.25, 22.45})}

	q := `insert into geocoding_result (query, coords) values ('A', $1)` // FAILS
	// q := `insert into geocoding_result (query, coords) values ('A', ($1)::bytea)` // WORKS
	// q := `insert into geocoding_result (query, coords) values ('A', ST_GeomFromEWKB($1))` // WORKS

	if _, err := pool.Exec(ctx, q, coords); err != nil {
		godump.Dd(err)

		// #*pgconn.PgError {
		//   +Severity            => "ERROR" #string
		//   +SeverityUnlocalized => "ERROR" #string
		//   +Code                => "XX000" #string
		//   +Message             => "parse error - invalid geometry" #string
		//   +Detail              => "" #string
		//   +Hint                => ""\x" <-- parse error at position 2 within geometry" #string
		//   +Position            => 0 #int32
		//   +InternalPosition    => 0 #int32
		//   +InternalQuery       => "" #string
		//   +Where               => "unnamed portal parameter $1 = '...'" #string
		//   +SchemaName          => "" #string
		//   +TableName           => "" #string
		//   +ColumnName          => "" #string
		//   +DataTypeName        => "" #string
		//   +ConstraintName      => "" #string
		//   +File                => "lwgeom_pg.c" #string
		//   +Line                => 294 #int32
		//   +Routine             => "pg_parser_errhint" #string
		// }
	}
}

func TestQueryPgxGeom(ctx context.Context, pool *pgxpool.Pool) {
	q := `select query, coords from geocoding_result` // FAILS
	// q := `select query, coords::bytea from geocoding_result` // WORKS
	// q := `select query, ST_AsBinary(coords) as "coords" from geocoding_result` // WORKS

	rows, _ := pool.Query(ctx, q)
	type Dest struct {
		Query  string
		Coords *ewkb.Point
	}
	res, err := pgx.CollectRows(rows, pgx.RowToStructByName[Dest])
	if err != nil {
		godump.Dd(err)

		// #pgx.ScanArgError {
		//   +ColumnIndex => 1 #int
		//   +FieldName   => "coords" #string
		//   +Err         => #ewkb.ErrExpectedByteSlice {
		//     +Value     => "0101000020E610000029AE2AFBAE74344081423D7D04604640" #string
		//   }
		// }
	}

	godump.Dump(res)
}

Example 2 using github.com/paulmach/orb

func TestInsertOrb(ctx context.Context, pool *pgxpool.Pool) {
	coords := ewkb.Value(orb.Point{45.25, 22.45}, 4326)

	q := `insert into geocoding_result (query, coords) values ('A', $1)` // FAILS
	// q := `insert into geocoding_result (query, coords) values ('A', ($1)::bytea)` // WORKS
	// q := `insert into geocoding_result (query, coords) values ('A', ST_GeomFromEWKB($1))` // WORKS
	if _, err := pool.Exec(ctx, q, coords); err != nil {
		godump.Dd(err) // same err as *pgconn.PgError above
	}
}

func TestQueryOrb(ctx context.Context, pool *pgxpool.Pool) {
	q := `select query, coords from geocoding_result` // FAILS
	// q := `select query, coords::bytea from geocoding_result` // WORKS
	// q := `select query, ST_AsBinary(coords) as "coords" from geocoding_result` // WORKS

	rows, _ := pool.Query(ctx, q)
	type Dest struct {
		Query  string
		Coords ewkb.GeometryScanner
	}
	res, err := pgx.CollectRows(rows, pgx.RowToStructByName[Dest])
	if err != nil {
		godump.Dd(err)

		// #pgx.ScanArgError {
		//   +ColumnIndex => 1 #int
		//   +FieldName   => "coords" #string
		//   +Err         => #*errors.errorString {
		//     -s         => "wkb: scan value must be []byte" #string
		//   }
		// }
	}

	godump.Dump(res)
}

[jackc]

Not familiar with those libraries, but it looks like github.com/twpayne/pgx-geom needs to be registered with pgx. Is that done?

[dankobg]

Yes sorry i forgot to mention that i did that.

poolCfg.AfterConnect = func(ctx context.Context, c *pgx.Conn) error {
		if err := pgxgeom.Register(ctx, c); err != nil {
			return err
		}
		return nil
	}

And it technically works but i wanted to know if i need to always write those st_asbinary helpers because otherwise i would receive that hex string.

[jackc]

Ah. Sorry, I don't know. Not very familiar with PostGIS or those libraries.