douxian9706 2018-02-05 23:09
浏览 117
已采纳

如何使用lib / pq驱动程序插入NUMERIC字段类型?

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
  • 写回答

1条回答 默认 最新

  • dscojuxf69080 2018-02-06 01:15
    关注

    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.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 Arcgis相交分析无法绘制一个或多个图形
  • ¥15 seatunnel-web使用SQL组件时候后台报错,无法找到表格
  • ¥15 fpga自动售货机数码管(相关搜索:数字时钟)
  • ¥15 用前端向数据库插入数据,通过debug发现数据能走到后端,但是放行之后就会提示错误
  • ¥30 3天&7天&&15天&销量如何统计同一行
  • ¥30 帮我写一段可以读取LD2450数据并计算距离的Arduino代码
  • ¥15 飞机曲面部件如机翼,壁板等具体的孔位模型
  • ¥15 vs2019中数据导出问题
  • ¥20 云服务Linux系统TCP-MSS值修改?
  • ¥20 关于#单片机#的问题:项目:使用模拟iic与ov2640通讯环境:F407问题:读取的ID号总是0xff,自己调了调发现在读从机数据时,SDA线上并未有信号变化(语言-c语言)