Error converting duckdb.Decimal to float64

[olablt]

If I understand correctly, scan should convert duckdb.Decimal to float64 values?

I get the error:

sql: Scan error on column index 2, name "price": converting driver.Value type duckdb.Decimal ("{10 2 10000}") to a float64: invalid syntax

my program:

func main() {

    // open/create database
    db, err := sql.Open("duckdb", "apps/duckdb/nt.db")
    if err != nil {
        log.Fatal("Failed to connect to database:", err)
    }
    defer db.Close()

    // Create table
    _, err = db.Exec(`
        CREATE TABLE IF NOT EXISTS ticks (
        id INTEGER,
        time TIMESTAMP,
        price DECIMAL(10,2),
        bid DECIMAL(10,2),
        ask DECIMAL(10,2),
        qty DECIMAL(10,2),
        side VARCHAR(3)
    )`)
    if err != nil {
        log.Fatal(err)
    }

    // Insert some data in table
    _, err = db.Exec(`
    INSERT INTO ticks (id, time, price, bid, ask, qty, side)
    VALUES
    (1, '2022-01-01 09:00:00', 100.00, 99.99, 100.01, 1.00, 'Ask'),
    (2, '2022-01-01 09:00:01', 100.01, 100.00, 100.02, 1.00, 'Bid'),
    (3, '2022-01-01 09:00:02', 100.02, 100.01, 100.03, 1.00, 'Ask'),
    (4, '2022-01-01 09:00:03', 100.03, 100.02, 100.04, 1.00, 'Bid'),
    `)
    if err != nil {
        log.Fatal(err)
    }

    // query first row
    rows, err := db.Query("SELECT id, time, price, bid, ask, qty, side FROM ticks LIMIT 5")
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()
    // print results
    for rows.Next() {
        var id int
        var time time.Time
        var price float64
        var bid float64
        var ask float64
        var qty float64
        var side string
        err := rows.Scan(&id, &time, &price, &bid, &ask, &qty, &side)
        if err != nil {
            log.Fatal(err)
        }
        log.Println("ID:", id, "Time:", time, "Price:", price, "Bid:", bid, "Ask:", ask, "Qty:", qty, "Side:", side)
    }
}

[marcboeker]

Unfortunately it is not possible to scan a DECIMAL directly into a float64 value. You need to define price,bit,ask,qty as Decimal and than convert them manually to float64.

var id int
var time time.Time
var price duckdb.Decimal
var bid duckdb.Decimal
var ask duckdb.Decimal
var qty duckdb.Decimal
var side string
err := rows.Scan(&id, &time, &price, &bid, &ask, &qty, &side)
if err != nil {
    log.Fatal(err)
}
log.Println("ID:", id, "Time:", time, "Price:", price.Float64(), "Bid:", bid.Float64(), "Ask:", ask.Float64(), "Qty:", qty.Float64(), "Side:", side)