Basic pgtype help

[0xBradock]

Hello, I am kind of new to pgx and I am trying to understand how to use pgtype.
I am also using sqlc, but that should make no difference.
I am writing a test that writes a struct to a postgres db, then the db is supposed to return back the value.

I am finding the use quite verbose, but I might be missing some thing.
I need to convert Go types into pgtype types (i.e. string to pgtype.Text{String: city, Valid: true}) to write to the db.
Than, the value that I receive from the read is not a string and I need to convert it again.

// This is my CreateWeather function signature where I call
// [QueryRow](https://pkg.go.dev/github.com/jackc/pgx/[email protected]#Conn.QueryRow)
func (q *Queries) CreateWeather(ctx context.Context, arg CreateWeatherParams) (Weather, error) {
row := q.db.QueryRow(ctx, createWeather,
	arg.Temperature,
	arg.City,
	arg.LocalTime,
	arg.Updated,
)
var i Weather
err := row.Scan(
	&i.ID,
	&i.Temperature,
	&i.City,
	&i.LocalTime,
	&i.Updated,
)
return i, err
}

// Invoking the above function in my test
temp := 12
city := "London"

weatherResponse, err := q.CreateWeather(ctx, CreateWeatherParams{
	Temperature: pgtype.Numeric{Int: big.NewInt(int64(temp)), Exp: 0, Valid: true},
	City:        pgtype.Text{String: city, Valid: true},
	LocalTime:   pgtype.Timestamp{Time: time.Now(), Valid: true},
	Updated:     pgtype.Date{Time: time.Now(), Valid: true},
})

// Then my tests:
require.Equal(t, temp, weatherResponse.Temperature)
require.Equal(t, city, weatherResponse.City)

Then my tests fail with:

expected: int(12)
actual  : pgtype.Numeric(pgtype.Numeric{Int:1200, Exp:-2, NaN:false, InfinityModifier:0, Valid:true})

expected: string("London")
actual  : pgtype.Text(pgtype.Text{String:"London", Valid:true})

Then, if I do fmt.Printf("Just City %+v\n", weatherResponse.City) I get pgtype.Text{String:"London", Valid:true}.

So, this means I have to do correctCityName := weatherResponse.City.String?

Then, if I use temp := 12 I get weatherResponse.Temperature = {Int:+1200 Exp:-2 NaN:false InfinityModifier:finite Valid:true}.

The only way I made it work was by using using Temperature pgtype.Int4, then in my test I would call CreateWeather with Temperature: pgtype.Int4{Int32: int32(temp), Valid: true},, then assert with require.Equal(t, temp, int(weatherResponse.Temperature.Int32)).

So, is it normal to have go struct => pgtype => db, then db => pgtype => go struct?

I am really confused on how to use correctly the library, any help is appreciated.

Thanks,

[jackc]

I haven't used sqlc, but with pgx you can use directly use normal Go types such as string, int64, and float64. Typically, I only use the pgtype types when I need to handle null values.