How to use pgtype.Numeric with gorm and sqlite3?

I need to store very large and high precision numbers with gORM, and using a pgtype.Numeric seems like the best bet. However, I cannot because I get an error: sql: Scan error on column index 4, name "foo": cannot scan int64

My model looks something like this:

type Model struct {
    gorm.Model
    Foo *pgtype.Numeric `gorm:"not null"`
}

Not sure if using pgtype.Numeric is the best (that's what i've seen everyone else use), or I'm doing something wrong. Thanks!

The code that caused the error:

package main

import (
  "gorm.io/driver/sqlite"
  "gorm.io/gorm"
  "math/big"

  "github.com/jackc/pgtype"
)

type Model struct {
  gorm.Model
  Foo *pgtype.Numeric `gorm:"not null"`
}

func main() {
  db, err := gorm.Open(sqlite.Open("test.db"), &gorm.Config{})
  if err != nil {
    panic("failed to connect database")
  }
  // Migrate the schema
  db.AutoMigrate(&Model{})

  // Create
  db.Create(&Model{Foo: &pgtype.Numeric{Int: big.NewInt(10000000), Status: pgtype.Present}})

  var m Model
  db.First(&m) // this line causes the error
}

Sqlite3 does not support big integer so there is no way you can accomplish that directly. I run the code and foo column is create as:

`foo` numeric NOT NULL

Which in sqlite https://www.sqlite.org/datatype3.html means

> A column with NUMERIC affinity may contain values using all five storage classes... If the TEXT value is a well-formed integer literal that is too large to fit in a 64-bit signed integer, it is converted to REAL.

So your big int will turn into float64. Good thing it paniced instead of losing accuracy silently.

What you can do is convert the big int to string or bytes first and store that.

When debugging the sql.Scanner interface for database deserialization, it is noticeable that the value from the database arrives either as int64 or float64. This then leads to the corresponding error message.

A possible solution is to use a text data type in the database, by adding the type text to the field tag:

`gorm: "type:text;"` 

Using the github.com/shopspring/decimal package, you can conveniently create a decimal number using the NewString function.

The adapted code to insert the data:

num, err := decimal.NewFromString("123456789012345.12345678901")
if err != nil {
	panic(err)
}
db.Create(&Model{Foo: &num})

The model structure might then look something like this:

type Model struct {
	gorm.Model
	Foo *decimal.Decimal `gorm: "not null;type:text;"`
}

This would result in the following schema:

Test

If one inserts a breakpoint in decimal.Scan, one can see that the value comes from the database as expected as a string, resulting in the creation of a decimal with NewFromString (see Decimal's scan method).

If you add this line of code to the end of the main function

fmt.Println(m.Foo)

it would result in the following output in the debug console:

123456789012345.12345678901

Complete Program

Your complete program, slightly adapted to the above points, would then look something like this:

package main

import (
	"fmt"
	"github.com/shopspring/decimal"
	"gorm.io/driver/sqlite"
	"gorm.io/gorm"
)

type Model struct {
	gorm.Model
	Foo *decimal.Decimal `gorm:"not null;type:text;"`
}

func main() {
	db, err := gorm.Open(sqlite.Open("test.db"), &gorm.Config{})
	if err != nil {
		panic("failed to connect database")
	}
	// Migrate the schema
	db.AutoMigrate(&Model{})

	// Create
	num, err := decimal.NewFromString("123456789012345.12345678901")
	if err != nil {
		panic(err)
	}
	db.Create(&Model{Foo: &num})

	var m Model
	db.First(&m)
	fmt.Println(m.Foo)
}

pgtype.Numeric and SQLite

If a PostgreSQL database is used, gorm can be used together with pgtype.Numeric to handle decimal numbers like 123456789012345.12345678901. You just need to use the numeric data type on the Postgres side with the appropriate desired precision (e.g. numeric(50,15)).

After all, this is exactly what pgtype is for, see the pgtype readme where it says:
>pgtype is the type system underlying the https://github.com/jackc/pgx PostgreSQL driver.

However, if you use a text data type in SQLite for the reasons mentioned above, pgtype.Numeric will not work with SQLite. An attempt with the above number writes 12345678901234512345678901e-11 to the DB and when reading it out the following error occurs:

sql: Scan error on column index 4, name "foo": 12345678901234512345678901e-11 is not a number