How to Import Rows to PostgreSQL from STDIN in Go?-Golang-php.cn

How to Import Rows to PostgreSQL from STDIN in Go?

Linda Hamilton

Release： 2024-10-24 14:20:53

Original

895 people have browsed it

How to Import Rows to PostgreSQL from STDIN in Go?

Importing Rows to PostgreSQL from STDIN

In Python, the subprocess module provides a convenient way to write data to PostgreSQL from STDIN using the COPY command. However, achieving this same functionality in Go involves a different approach.

The official documentation for the github.com/lib/pq package offers an example of how to import records directly from STDIN using the CopyIn function. Here's an adapted version of the provided code:

<code class="go">package main

import (
    "database/sql"
    "fmt"
    "log"

    "github.com/lib/pq"
)

func main() {
    records := [][]string{
        {"Rob", "Pike"},
        {"Ken", "Thompson"},
        {"Robert", "Griesemer"},
    }

    db, err := sql.Open("postgres", "dbname=postgres user=postgres password=postgres")
    if err != nil {
        log.Fatalf("open: %v", err)
    }
    if err = db.Ping(); err != nil {
        log.Fatalf("open ping: %v", err)
    }
    defer db.Close()

    txn, err := db.Begin()
    if err != nil {
        log.Fatalf("begin: %v", err)
    }

    stmt, err := txn.Prepare(pq.CopyIn("test", "first_name", "last_name"))
    if err != nil {
        log.Fatalf("prepare: %v", err)
    }

    for _, r := range records {
        _, err = stmt.Exec(r[0], r[1])
        if err != nil {
            log.Fatalf("exec: %v", err)
        }
    }

    _, err = stmt.Exec()
    if err != nil {
        log.Fatalf("exec: %v", err)
    }

    err = stmt.Close()
    if err != nil {
        log.Fatalf("stmt close: %v", err)
    }

    err = txn.Commit()
    if err != nil {
        log.Fatalf("commit: %v", err)
    }

    log.Println("Records successfully imported")
}</code>

Copy after login

This revised code uses a transaction to ensure atomicity, consistency, isolation, and durability (ACID) properties during the import process. The CopyIn function allows you to specify the target table name and the columns to which the data will be inserted.

The provided sample data is then inserted into the "test" table using prepared statements. Prepared statements enhance performance by precompiling the SQL query, reducing the overhead associated with parsing and planning.

Once all records are inserted, the transaction is committed and the data is permanently stored in the database.

The above is the detailed content of How to Import Rows to PostgreSQL from STDIN in Go?. For more information, please follow other related articles on the PHP Chinese website!