cannot scan timestamp (OID 1114) in binary format into *string

[xakepp35]

Describe the bug
I am using protobuf generated structures, and it does not have time.Time type. However it have string type, which is sufficient for my goals. But library does not allow to scan timestamp as string. How can i do so? I cannot modify Response struct, as its a generated code. Also i dont want to copy from intermediate struct.

To Reproduce
try to scan timestamp column to *string field

package main

import (
	"context"
	"log"
	"os"

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

type Response struct {
	CreatedAt  string `protobuf:"bytes,11,opt,name=created_at,json=createdAt,proto3" json:"created_at,omitempty"`    // created_at date,
}

func main() {
	conn, err := pgx.Connect(context.Background(), os.Getenv("DATABASE_URL"))
	if err != nil {
		log.Fatal(err)
	}
	defer conn.Close(context.Background())
	var res Response
	err :=  conn.QueryRow(context.Background(), "SELECT created_at FROM table").Scan(&res.CreatedAt)
	if err != nil {
		panic(err.Error()) // <-- err will follow caption
	}
}

Expected behavior
CreatedAt containing timestamp in RFC3339Nano string format

Actual behavior
non-nil err, containing message: "cannot scan timestamp (OID 1114) in binary format into *string"

[jackc]

Here are some things you could try in rough order of difficulty:

1. Cast the timestamp to string in your query (e.g. SELECT created_at::text FROM table).
2. Tell pgx to use the text format all the time by changing the default query exec mode to QueryExecModeExec.
3. Make your own string backed type that implements Timestamp(tz)Scanner. Cast your string to it when you scan. See the zeronull package for an example of that style.
4. Copy the Timestamp(tz)Codec from pgx into your project, change it to allow scanning to a string, and register your new Codec on the connection.
5. Create a new TryWrapScanPlanFunc that implements your custom behavior.

[hmoazzem]

Just ran into it, and somehow got it working.

type SomeObject struct {
  OID pgtype.Int8 `json:"oid"`
}

Now SomeObject.OID.Int64 does give desired OID. @jackc what you think of it?

[jackc]

@hmoazzem I don't see how that code relates to scanning a binary timestamp into a *string.

[hmoazzem]

Maybe after looking at several issues I lost track of relevance. I was getting error scanning OID field, so searched and looked at many issues with OID in title

Anyway, what go data type would you (recommend to) use for OID?

[jackc]

@hmoazzem uint32 or https://pkg.go.dev/github.com/jackc/pgx/[email protected]/pgtype#Uint32 if you need it to be nullable.