Working with pgtypes

[carlosmaranje]

When using pgx/v5 the INSERT clause for the following schema:

CREATE TABLE "items"
(
    "id"                serial PRIMARY KEY,
    "uuid"              uuid    NOT NULL,
    "weight"            decimal,
    "name"              text    NOT NULL,
    "description"       text,
    "created_at"        timestamp DEFAULT now(),
    "deleted_at"        timestamp
);

Will generate this output:

type CreateItemParams struct {
	Uuid             uuid.UUID        `json:"uuid"`
	Weight           pgtype.Numeric   `json:"weight"`
	Name             string           `json:"name"`
	Description      pgtype.Text      `json:"description"`
	CreatedAt        pgtype.Timestamp `json:"created_at"`
	DeletedAt        pgtype.Timestamp `json:"deleted_at"`
}

func (q *Queries) CreateItem(ctx context.Context, arg CreateItemParams) (Item, error) {

...
...

}

I am having a very hard time trying to use the CreateItem() method using pgtypes. I have found online that for text columns I can do:

description := pgtype.Text{
		String: "Test string",
		Valid:  true,
	}

However, for pgtype.Numeric it is more complicated since, for example, to represent 4.53 it seems like I have to do:

    weight := pgtype.Numeric{
		Int:    big.NewInt(453),
		Exp:    -2,
		Status: pgtype.Present,
	}

I do not want to use overrides in the sqlc configuration, since pgtypes are intented to offer better results than Go's built-in types. However using pgtype 'as-is' I think it would require to create some methods to convert Go types to pgtypes.

My question is: Am I missing something here? Is this the way it is supposed to work? How do you usually work with these types? Any real-use example would be ideal.

[oliviernguyenquoc]

Same issue with pgtype.Numeric generated by SQLC. I found the structure difficult to manipulate when trying to insert a float.

The only way I found is the way you mention, as changing type in DDL (like going from Numeric type to Float8) does not change the generated type (pgtype.Numeric):

pgtype.Numeric{
		Int:    big.NewInt(453),
		Exp:    -2,
		Status: pgtype.Present,
}

[rmerli]

Same thing here, is there anyone that can point us in the right direction?

[m-ocean-it]

Here's a working solution for creating pgtype.Numeric (or other types):

	package main

	import (
		"fmt"

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

	func main() {
		f := 5.527

		num := &pgtype.Numeric{}
		err := num.Scan(fmt.Sprintf("%.2f", f)) // .2 specifies precision
		if err != nil {
			panic(err)
		}

		fmt.Printf("%+v\n", num)
	}

Result:

&{Int:+553 Exp:-2 NaN:false InfinityModifier:finite Valid:true}

Inspired by this answer on StackOverflow.

[carlosmaranje]

Yeah I also cross-posted this on SO and that was the answer I got. We ended up doing something like that. I'll leave it here too, in case it can help someone.

func Numeric(number float64) (value pgtype.Numeric) {
	parse := strconv.FormatFloat(number, 'f', -1, 64)
	if err := value.Scan(parse); err != nil {
		log.Fatal().Err(err).Msg("Error scanning numeric")
	}
	return value
}

func NumericNull() pgtype.Numeric {
	return pgtype.Numeric{
		Int:              nil,
		Exp:              0,
		NaN:              false,
		InfinityModifier: 0,
		Valid:            false,
	}
}

We just use those methods everywhere.

Note that we are being verbose on purpose. You can always use the first method and take valid as a param.

[burxtx]

How to convert pgtype.Numeric to base 10 string? pgtype v4 has a method AssignTo can do this, but this method is missing in v5.

[m-ocean-it]

How about:

// num is of type pgtype.Numeric
val, err := num.Value()
if err != nil {
	panic(err)
}
s := val.(string)