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

Pontifical

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? – Butterfat

yes i don't want to loop through csv records – Pontifical

also don't want to make multiple insert records in for loop for each record of csv – Pontifical

Use batch insert. – Maraud

can you able to provide any reference for the same ? – Pontifical

You can use COPY. – Butterfat

yes, but i have to use sql raw query for this – Pontifical

If not SQL, what do you want to use then? Are you using some ORM? – Butterfat

i want to do the same in golang .. please check edit – Pontifical

Standard golang executes raw SQL to insert records into a database. – Butterfat

as i said before i don't want to use raw SQL to insert recrods into a database – Pontifical

As mentioned, use batch insert, then. – Severable

Answer 1

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)
    }
}

Clite

I'm trying to avoid defining schema. I want to be able to export data and just re import without having to deal with schema in between. – Mite

This solution will consume a lot of memory. – Slobber

This solution may be used with batches. – Slobber

what about files with 10Gb size? – Sturges

Answer 2

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())

Pasquinade

Not sure if its due to me using pgx/v4 but this doesn't compile. This part doesn't: dbconn.Conn().PgConn().CopyFrom Which pgx version should be used? – Mite

Try to use this approach https://mcmap.net/q/1922519/-import-data-into-postgresql-and-export-to-csv – Pasquinade

Thanks! That was my post, figured it out from you did. I had to specifically use v1 of pgx instead of the latest v4. Thanks @Pavlo Golub – Mite

Oh, I see. I use v4 for sure. But maybe I have misprint in my code example since I was extracting it from the working project. You may find it here: github.com/cybertec-postgresql/pg_timetable/blob/master/… – Pasquinade