inserting into postgresql decimal field fails with error "cannot convert {125.00} to Int2"-CodePudding

I am fairly new to golang. I am trying to insert into a postgresql table which has a numeric field, using a web app gin-gonic.

postgres=# \d user_txns;
                       Table "public.user_txns"
   Column    |         Type          | Collation | Nullable | Default
------------- ----------------------- ----------- ---------- ---------
 user_id     | character varying(15) |           | not null |
 txn_code    | smallint              |           | not null |
 description | character varying(64) |           | not null |
 txn_amount  | numeric(15,4)         |           | not null |
 txn_type    | smallint              |           | not null |
 voucher     | character varying(16) |           |          |

I am using jackc pgxpool to insert into the table as below.

109 ▏ sql := `INSERT INTO user_txns VALUES ($1,$2, $3, $4, $5)`
▎ 110 ▏ _, err = tx.Exec(context.Background(), sql,
▎ 111 ▏ ▏ ▏ ▏ ▏ ▏ claims["phone"],
▎ 112 ▏ ▏ ▏ ▏ ▏ ▏ recharge,
▎ 113 ▏ ▏ ▏ ▏ ▏ ▏ "User recharge",
▎ 114 ▏ ▏ ▏ ▏ ▏ ▏ recharge.Amount,
▎ 115 ▏ ▏ ▏ ▏ ▏ ▏ credit,
▎ 116 ▏ )
▎ 117 ▏ if err != nil {
▎ 118 ▏ ▏ c.JSON(http.StatusInternalServerError, gin.H{"msg": err.Error()})
▎ 119 ▏ ▏ return
▎ 120 ▏ },

The payload is a json request that has the following structure:

{
  "amount": 125.00 
}

I unmarshal the request into a struct defined as below.

type Recharge struct {
  Amount string `json:"amount" binding:"required"`
}

The insert fails with error

"msg": "cannot convert {125} to Int2"

What is the right golang data type to use for inserting into a decimal field?

thanx

CodePudding user response：

The easiest way to insert a value like 125.00 into a postgres column of type numeric would be to use a float type in Go. This works out of the box and so there's no need to implement any kind of custom interface.

For example:

CREATE TABLE t (
    id serial PRIMARY KEY
    , amount numeric(15,4) NOT NULL
    -- ...
);

data := []byte(`{"amount": 125.00}`)
var obj struct {
    Amount float64 `json:"amount"`
}
if err := json.Unmarshal(data, &obj); err != nil {
    panic(err)
}

_, err := db.Exec(`INSERT INTO t (amount) VALUES ($1)`, obj.Amount)

Float types are subject to rounding errors however, and because of that a common practice for storing monetary amounts is to use integers that represent the value in cents. E.g. 125.00 becomes 12500. This also works out of the box.

For example:

CREATE TABLE t (
    id serial PRIMARY KEY
    , amount int8 NOT NULL
    -- ...
);

data := []byte(`{"amount": 12500}`)
var obj struct {
    Amount int64 `json:"amount"`
}
if err := json.Unmarshal(data, &obj); err != nil {
    panic(err)
}

_, err := db.Exec(`INSERT INTO t (amount) VALUES ($1)`, obj.Amount)

If you want to use the pgtype.Numeric for storing and retrieving the amount to and from the database then you'll have to do some extra work because pgtype.Numeric doesn't know how to encode/decode JSON 125.00/"125.00" values.

One thing you can do is to declare a custom struct type, have it embed the pgtype.Numeric type, and then have the custom struct type implement the json.Marshaler and json.Unmarshaler interfaces.

For example:

CREATE TABLE t (
    id serial PRIMARY KEY
    , amount numeric(15,4) NOT NULL
    -- ...
);

type MyNumeric struct {
    pgtype.Numeric
}

func (n *MyNumeric) UnmarshalJSON(data []byte) error {
    var s json.Number
    if err := json.Unmarshal(data, &s); err != nil {
        return err
    }
    return n.Numeric.Set(s.String())
}

func (n MyNumeric) MarshalJSON() ([]byte, error) {
    var f float64
    if err := n.Numeric.AssignTo(&f); err != nil {
        return nil, err
    }
    return []byte(strconv.FormatFloat(f, 'f', -1, 64)), nil
}

data := []byte(`{"amount": 125.00}`)
var obj struct {
    Amount MyNumeric `json:"amount"`
}
if err := json.Unmarshal(data, &obj); err != nil {
    panic(err)
}

_, err := db.Exec(`INSERT INTO t (amount) VALUES ($1)`, obj.Amount)