Decimal type in Go and Postgresql with gorm

In Go, when interacting with the PostgreSQL database using GORM, handling decimal types (typically used for representing monetary values or other data requiring precise decimal representation) is a common requirement. In PostgreSQL, decimal types are typically represented by the DECIMAL or NUMERIC types, which can store exact numerical values and allow specifying precision (total number of digits) and scale (number of digits after the decimal point).

In Go, since the language itself does not directly support decimal types, we typically use libraries such as shopspring/decimal to handle decimal numbers requiring high precision. This library provides a Decimal type to support high-precision arithmetic operations.

However, to use this type with PostgreSQL's DECIMAL or NUMERIC types in GORM, we need to perform some adaptation to ensure data is correctly transferred between the Go application and the database.

Example

First, you need to import the shopspring/decimal library:

go
import (
    "github.com/shopspring/decimal"
)

Then, define your model. When defining models with GORM, you can directly use the decimal.Decimal type:

go
type Product struct {
    gorm.Model
    Price decimal.Decimal `gorm:"type:decimal(10,2);"` // Specifies the database column type as decimal(10,2), meaning it can store up to 10 digits in total, with 2 digits after the decimal point.
}

In the above code, the Price field is defined as the decimal.Decimal type and the corresponding database column type decimal(10,2) is specified via the gorm tag, meaning this field can store up to 10 digits in total, with 2 digits after the decimal point.

Data Reading and Writing

Next, when you need to write data to the database or read data from it, GORM and the shopspring/decimal library work well together without requiring additional data conversion:

go
func createProduct(db *gorm.DB, price decimal.Decimal) error {
    product := Product{Price: price}
    result := db.Create(&product)
    return result.Error
}

func getProduct(db *gorm.DB, id uint) (*Product, error) {
    var product Product
    result := db.First(&product, id)
    if result.Error != nil {
        return nil, result.Error
    }
    return &product, nil
}

// Example: Create and retrieve a product
db, _ := gorm.Open(postgres.Open("dsn"), &gorm.Config{})
createProduct(db, decimal.NewFromFloat(99.99))
product, _ := getProduct(db, 1)
fmt.Println("Product price:", product.Price)

In the above example, we create a new Product instance, set the price, and save it to the database. Then, we retrieve the product's information from the database and print the price. During this process, the decimal.Decimal type seamlessly corresponds with PostgreSQL's DECIMAL type, ensuring data precision.

Thus, you can use GORM to handle decimal types in Go and PostgreSQL. This is crucial for applications that need to process financial data or other data requiring high-precision calculations and storage.