I've written a toy app to experiment with using Postgresql through sqlx. I got a mass insert working using
pq.CopyIn
as content of a prepared statement
stmt, _ := tx.Preparex(pq.CopyIn(tablename, column, column, ...)
I would then proceed to add rows to the mass insert I'm creating.
tx.Exec(..., ..., ...)
then finally execute the prepared statement
stmt.Exec()
This worked perfectly before, but now I've come back to it and try and execute this code, it hangs on the
stmt.Exec
Am I missing something in my code or is this all to do with the Database Engine, being unresponsive.
Here's my full code for this.
package main
import (
_ "database/sql"
"fmt"
"log"
"encoding/json"
"io/ioutil"
"os"
"github.com/jmoiron/sqlx"
"github.com/lib/pq"
)
var schema = `
CREATE TABLE IF NOT EXISTS contact (
id Serial,
first_name text,
last_name text,
email text
);`
type Contact struct {
Id int `json:"-"`
First_name string `json:"first_name"`
Last_name string `json:"last_name"`
Email string `json:"email"`
}
type Contacts struct {
Contacts []Contact `json:"contacts"`
}
func (c *Contacts) createFromJSON(json_str []byte) error {
b := []byte(json_str)
err := json.Unmarshal(b, &c)
if err != nil {
log.Fatal(err)
}
return err
}
func (c *Contacts) save(db *sqlx.DB) error {
tx := db.MustBegin()
stmt, _ := tx.Preparex(pq.CopyIn("contact", "first_name", "last_name", "email"))
for _, contact := range c.Contacts {
tx.Exec(contact.First_name, contact.Last_name, contact.Email)
}
_, err := stmt.Exec()
if err != nil {
log.Fatal(err)
return err
}
err = stmt.Close()
if err != nil {
log.Fatal(err)
return err
}
tx.Commit()
return nil
}
func connect() (*sqlx.DB, error) {
db, err := sqlx.Connect("postgres", "user=pqgotest dbname=pqgotest sslmode=disable")
if err != nil {
log.Fatal(err)
}
return db, err
}
func createTables(db *sqlx.DB) {
db.MustExec(schema)
}
func main() {
db, err := connect()
if err != nil {
os.Exit(1)
}
createTables(db)
contactsJson, e := ioutil.ReadFile("./contacts.json")
if e != nil {
fmt.Printf("File error: %v
", e)
os.Exit(1)
}
tx := db.MustBegin()
tx.MustExec("DELETE FROM contact")
tx.Commit()
contacts := new(Contacts)
contacts.createFromJSON(contactsJson)
contacts.save(db)
people := new(Contacts)
db.Select(people.Contacts, "SELECT * FROM contact ORDER BY email,id ASC")
for _, contact := range people.Contacts {
contact_json, err := json.Marshal(contact)
if err != nil {
log.Fatal(err)
os.Exit(1)
}
fmt.Printf("%s
", contact_json)
}
}
I could include the contents of the contacts.json file as well, if that will help.
UPDATE
Yes it was obvious in the end. I was creating a statement from tx,
stmt, _ := tx.Preparex(pq.CopyIn(tablename, column, column, ...)
and further additions to this should be to stmt
stmt.Exec(..., ..., ...)
Also another error not directly related to the question is where I insert an array of contacts into the Contacts field of the struct Contacts
people := new(Contacts)
db.Select(people.Contacts, "SELECT * FROM contact ORDER BY email,id ASC")
should be passing a pointer to the Select method of db of the Contacts array field of Contacts, like so
db.Select(&people.Contacts, "SELECT * FROM contact ORDER BY email,id ASC")
In case people try and run this code later and wonder why it's not printing the results to the console.
