Bulk insert from csv in postgres using golang without using for loop

Question

I am new in Golang,

I have csv file which have data looks like this

field1,field2
1.1,2
1.2,3
1.3,2

i want to insert all csv data into db table using golang without using for loop .. i am using postgres database

also don't want to use sql raw query

i am using gorm ORM

Can you show what you've tried so far? What loop specifically are you trying to avoid? The loop reading the csv records, the loop inserting the individual records? Can you show what data structure you're using to represent the csv data in Go? — mkopriva, Mar 24 '21 at 10:49
also don't want to make multiple insert records in for loop for each record of csv — M_x, Mar 24 '21 at 10:55
You can use [`COPY`](https://stackoverflow.com/questions/2987433/how-to-import-csv-file-data-into-a-postgresql-table). — mkopriva, Mar 24 '21 at 11:00
If not SQL, what do you want to use then? Are you using some ORM? — mkopriva, Mar 24 '21 at 11:04
Standard golang executes raw SQL to insert records into a database. — mkopriva, Mar 24 '21 at 11:06
as i said before i don't want to use raw SQL to insert recrods into a database — M_x, Mar 24 '21 at 11:08
As mentioned, use [batch insert](https://gorm.io/docs/create.html#Batch-Insert), then. — Riwen, Mar 24 '21 at 11:54

score 6 · Answer 1 · answered Mar 26 '21 at 15:18

You can use pgx library for that:

    filename := "foo.csv"
    dbconn, err := pgx.Connect(context.Background(), os.Getenv("DATABASE_URL"))
    if err != nil {
        panic(err)
    }
    defer dbconn.Release()
    f, err := os.Open(filename)
    if err != nil {
        panic(err)
    }
    defer func() { _ = f.Close() }()
    res, err := dbconn.Conn().PgConn().CopyFrom(context.Background(), f, "COPY csv_test FROM STDIN (FORMAT csv)")
    if err != nil {
        panic(err)
    }
    fmt.Print(res.RowsAffected())

Arthur Chaloin · Answer 2 · 2021-04-08T14:59:09.363

You can parse the CSV into a slice of typed structures using an external library, and then insert them all at once using GORM's batch insert.

Here is an example :

import (
    "os"

    "github.com/gocarina/gocsv"
    "gorm.io/driver/postgres"
    "gorm.io/gorm"
)

// Entry defines both the CSV layout and database schema
type Entry struct {
    gorm.Model

    Field1 float64 `csv:"field1"`
    Field2 float64 `csv:"field2"`
}

func main() {
    // Open the CSV file for reading
    file, err := os.Open("data.csv")
    if err != nil {
        panic(err)
    }
    defer file.Close()

    // Parse CSV into a slice of typed data `[]Entry` (just like json.Unmarshal() does)
    // The builtin package `encoding/csv` does not support unmarshaling into a struct
    // so you need to use an external library to avoid writing for-loops.
    var entries []Entry
    err = gocsv.Unmarshal(file, &entries)
    if err != nil {
        panic(err)
    }

    // Open a postgres database connection using GORM
    db, err := gorm.Open(postgres.Open("host=localhost user=postgres password=dev dbname=foo port=5432 sslmode=disable TimeZone=Europe/Paris"))
    if err != nil {
        panic(err)
    }

    // Create `entries` table if not exists
    err = db.AutoMigrate(&Entry{})
    if err != nil {
        panic(err)
    }

    // Save all the records at once in the database
    result := db.Create(entries)
    if result.Error != nil {
        panic(result.Error)
    }
}

Bulk insert from csv in postgres using golang without using for loop

2 Answers2