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 11 · Answer 1 · answered Mar 26 '21 at 15:18

11

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

answered Mar 26 '21 at 15:18

Pavlo Golub

359
5
13

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? – ozn Aug 10 '21 at 21:27
Try to use this approach https://stackoverflow.com/a/68749440/1109280 – Pavlo Golub Aug 12 '21 at 10:33
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 – ozn Aug 12 '21 at 17:46
1

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: https://github.com/cybertec-postgresql/pg_timetable/blob/master/internal/pgengine/copy.go – Pavlo Golub Aug 17 '21 at 09:19

Arthur Chaloin · Accepted Answer · 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)
    }
}

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. — ozn, Aug 10 '21 at 21:28

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

2 Answers2

Linked