Help Center/
GaussDB/
Best Practices/
Best Practices for Go/
Best Practices for Go (Centralized Instances)/
Procedure/
Complete Example
Updated on 2025-09-04 GMT+08:00
Complete Example
The following uses a DSN connection string as an example to explain how to initialize the employee table, insert data in batches, and obtain column information in the result set:
// main.go package main import ( "database/sql" "fmt" _ "gitee.com/opengauss/openGauss-connector-go-pq" "log" "os" ) func main() { // Create a database object. hostip := os.Getenv("GOHOSTIP") // GOHOSTIP: IP address written to environment variables. port := os.Getenv("GOPORT") // GOPORT: port number written to environment variables. usrname := os.Getenv("GOUSRNAME") // GOUSRNAME: username written to environment variables. passwd := os.Getenv("GOPASSWD") // GOPASSWD: user password written to environment variables. dbname := os.Getenv("GODBNAME") // GODBNAME: name of the target database written to environment variables. connect_timeout := os.Getenv("GOCONNECT_TIMEOUT") // GOCONNECT_TIMEOUT: timeout interval for connecting to the database written to environment variables. socket_timeout := os.Getenv("GOSOCKET_TIMEOUT") // GOSOCKET_TIMEOUT: maximum duration of the SQL statement written to environment variables. rootcertPath := os.Getenv("GOROOTCERT") // GOROOTCERT: path to the root certificate written to environment variables. sslkeyPath := os.Getenv("GOSSLKEY") // GOSSLKEY: path to the key of the client certificate written to environment variables. sslcertPath := os.Getenv("GOSSLCERT") // GOSSLCERT: path to the client SSL certificate written to environment variables. sslmode := os.Getenv("GOSSLMODE") // GOSSLMODE: SSL encryption written to environment variables. dsn := fmt.Sprintf("host=%s port=%s user=%s password=%s dbname=%s connect_timeout=%s socketTimeout=%s " + "sslmode=%s sslrootcert=%s sslkey=%s sslcert=%s target_session_attrs=master", hostip, port, usrname, passwd, dbname, connect_timeout, socket_timeout, sslmode, rootcertPath, sslkeyPath, sslcertPath, ) db, err := sql.Open("gaussdb", dsn) if err != nil { panic(err) } defer db.Close() err = db.Ping() if err != nil { panic(err) } fmt.Println("connect success.") // Start a transaction. tx, err := db.Begin() if err != nil { log.Fatal(err) return } // Initialize a data table. _, err = tx.Exec("drop table if exists employee;") if err != nil { fmt.Println("drop table employee failed, err:", err) err = tx.Rollback() // On error, roll back the transaction. return } fmt.Println("drop table employee success.") _, err = tx.Exec("create table employee (id SERIAL PRIMARY KEY, name varchar(20), age int, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);") if err != nil { fmt.Println("create table employee failed, err:", err) err = tx.Rollback() return } fmt.Println("create table employee success.") // Insert data in batches. employee := []struct { Name string Age uint8 }{{Name: "zhangsan", Age: 21}, {Name: "lisi", Age: 22}, {Name: "zhaowu", Age: 23}} batchSql := "INSERT INTO employee (name, age) VALUES " vals := []interface{}{} placeholders := "(?, ?)" for _, u := range employee { vals = append(vals, u.Name, u.Age) } stmt := batchSql + placeholders _, err = tx.Exec(stmt, vals...) if err != nil { fmt.Println("batch insert into table employee failed, err:", err) err = tx.Rollback() return } fmt.Println("batch insert into table employee success.") // Commit the transaction. err = tx.Commit() if err != nil { fmt.Println("commit failed, err:", err) err = tx.Rollback() log.Fatal(err) return } fmt.Println("commit success.") // Obtain column information in the result set. rows, err := db.Query("SELECT id, name, created_at FROM employee;") if err != nil { fmt.Println("query table employee failed, err:", err) return } columns, err := rows.Columns() if err != nil { fmt.Println("get query rows columns failed, err:", err) return } fmt.Println("Column count: ", len(columns)) fmt.Println("Column name list: ", columns) fmt.Println("--------------------------") // Obtain column types. columnTypes, err := rows.ColumnTypes() if err != nil { fmt.Println("get query rows ColumnTypes failed, err:", err) return } for _, ct := range columnTypes { fmt.Println("Column name: ", ct.Name()) fmt.Println("Database type: ", ct.DatabaseTypeName()) length, ok := ct.Length() if ok { fmt.Println("Length: ", length) } precision, scale, ok := ct.DecimalSize() if ok { fmt.Printf("Precision/Scale: %d/%d\n", precision, scale) } nullable, ok := ct.Nullable() if ok { fmt.Println("Nullable: ", nullable) } fmt.Println("Go type: ", ct.ScanType()) fmt.Println("-----") } }
Result Verification
Below are the execution results for Complete Example:
connect success. drop table employee success. create table employee success. batch insert into table employee success. commit success. Column count: 3 Column name list: [id name created_at] -------------------------- Column name: id Database type: INT4 Go type: int32 ----- Column name: name Database type: VARCHAR Length: 20 Go type: string ----- Column name: created_at Database type: TIMESTAMP Go type: time.Time -----
The following tasks have been completed as expected:
- Concatenate the database connection string, create a connection object using sql.Open, and verify the connection status through the db.Ping() method.
- Start a transaction and initialize the test table employee in the transaction.
- Construct employee test data in the transaction, generate an SQL statement for batch insertion, bind parameters through the Exec API provided by the transaction object, and send packets to the database to execute the SQL statement.
- After a successful batch insertion, call the Commit API to commit the transaction. (If the insertion fails, call the Rollback API to roll back the transaction.)
- Call the Query API provided by the database connection object "db" to query the batch insertion results. Call the Columns API provided by the result object "rows" to obtain the list of all column names. Call the ColumnTypes API provided by the result object "rows" to obtain metadata about columns in the result set.
Rollback Method
To roll back operations within a specific transaction, call the Rollback API of the transaction object.
Parent topic: Procedure
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
The system is busy. Please try again later.
For any further questions, feel free to contact us through the chatbot.
Chatbot