-
Notifications
You must be signed in to change notification settings - Fork 923
Working with pgtypes #2910
-
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.
Beta Was this translation helpful? Give feedback.
All reactions
-
👀 2
Replies: 4 comments 2 replies
-
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, }
Beta Was this translation helpful? Give feedback.
All reactions
-
Same thing here, is there anyone that can point us in the right direction?
Beta Was this translation helpful? Give feedback.
All reactions
-
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.
Beta Was this translation helpful? Give feedback.
All reactions
-
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
validas a param.
Beta Was this translation helpful? Give feedback.
All reactions
-
👍 4
-
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.
Beta Was this translation helpful? Give feedback.
All reactions
-
How about:
// num is of type pgtype.Numeric val, err := num.Value() if err != nil { panic(err) } s := val.(string)
Beta Was this translation helpful? Give feedback.