How to insert NUMERIC field type using lib/pq driver?

Question

I have a table where I have a field which stores a really big number (math.big, which is bigger than uint64). I am storing it in a DECIMAL type:

difficulty          NUMERIC     NOT NULL,

So, how do I insert this field from Go code using PQ library (github.com/lib/pq) ?

This code does not work:

me@desk:~/src/github.com/myapp/misc$ cat insertbig.go
package main

import (
    "database/sql"
    _ "github.com/lib/pq"
    "os"
    "log"
    "math/big"
)
func main() {
    var err error
    var db *sql.DB
    std_out:=log.New(os.Stdout,"",0)

    conn_str:="user='testuser' dbname='testdb' password='testpasswd'";
    db,err=sql.Open("postgres",conn_str);
    if (err!=nil) {
        log.Fatal(err);
    }
    _,err=db.Exec("CREATE TABLE bigtable(difficulty NUMERIC)");

    difficulty:=big.NewInt(0);
    difficulty.SetString("1111111111111111111111111111111111111111111111111111111111111111111111",10);
    _,err=db.Exec("INSERT INTO bigtable(difficulty) VALUES(?)",difficulty);
    if (err!=nil) {
        log.Fatal(err);
    } else {
        std_out.Println("record was inserted");
    }
}

me@desk:~/src/github.com/myapp/misc$ 

It gives me this error:

2018/02/05 17:00:25 sql: converting argument $1 type: unsupported type big.Int, a struct

Answer 1

First of all, you should use numeric placeholders ($1, $2, ...) with PostgreSQL since that's what it uses natively. As far as getting a bignum into a numeric column in the database, a quick run through the documentation and source suggests that your best bet is to use a string (which PostgreSQL will treat as a value of type "unknown") and let PostgreSQL parse it and cast it based on the (known) type of the column.

So something like this:

difficulty := "1111111111111111111111111111111111111111111111111111111111111111111111"
_, err = db.Exec("INSERT INTO bigtable (difficulty) VALUES ($1)", difficulty)

A similar approach would apply to any other PostgreSQL types that the driver doesn't natively understand; there will always be a string representation that you can use to make it go.

---

You could also type SQLBigInt big.Int and implement the driver.Valuer interface from database/sql/driver:

type SQLBigInt big.Int
func (i *SQLBigInt) Value() (driver.Value, error) {
    return (*big.Int)(i).String(), nil
}

// Or
type SQLBigInt struct {
    big.Int
}
func (i *SQLBigInt) Value() (driver.Value, error) {
    return i.String(), nil
}

and then probably sql.Scanner from "database/sql" for reading but that could get ugly and might not be worth the effort as you'd be wrapping and unwrapping all the time anyway.

Answer 2

you can implement valuer and scanner interface

//BigInt big.Int alias
   type BigInt struct {
      big.Int
   }

// Value implements the Valuer interface for BigInt
func (b *BigInt) Value() (driver.Value, error) {
   if b != nil {
      return b.String(), nil
   }
   return nil, nil
}

// Scan implements the Scanner interface for BigInt
func (b *BigInt) Scan(value interface{}) error {
    var i sql.NullString
    if err := i.Scan(value); err != nil {
        return err
    }
    if _, ok := b.SetString(i.String, 10); ok {
       return nil
    }
    return fmt.Errorf("Could not scan type %T into BigInt", value)
}