5.4 PostgreSQL

PostgreSQL is an object-relational database management system available for many platforms including Linux, FreeBSD, Solaris, Microsoft Windows and Mac OS X. It is released under an MIT-style license, and is thus free and open source software. It's larger than MySQL because it's designed for enterprise usage as an alternative to Oracle. Postgresql is a good choice for enterprise type projects.

PostgreSQL drivers

There are many database drivers available for PostgreSQL. Here are three examples of them:

I will use the first one in the examples that follow.

Samples

We create the following SQL:

    CREATE TABLE userinfo
    (
        uid serial NOT NULL,
        username character varying(100) NOT NULL,
        departname character varying(500) NOT NULL,
        Created date,
        CONSTRAINT userinfo_pkey PRIMARY KEY (uid)
    )
    WITH (OIDS=FALSE);

An example:

    package main

    import (
        "database/sql"
        "fmt"
        _ "github.com/lib/pq"
        "time"
    )

    const (
        DB_USER     = "postgres"
        DB_PASSWORD = "postgres"
        DB_NAME     = "test"
    )

    func main() {
        dbinfo := fmt.Sprintf("user=%s password=%s dbname=%s sslmode=disable",
            DB_USER, DB_PASSWORD, DB_NAME)
        db, err := sql.Open("postgres", dbinfo)
        checkErr(err)
        defer db.Close()

        fmt.Println("# Inserting values")

        var lastInsertId int
        err = db.QueryRow("INSERT INTO userinfo(username,departname,created) VALUES($1,$2,$3) returning uid;", "astaxie", "研发部门", "2012-12-09").Scan(&lastInsertId)
        checkErr(err)
        fmt.Println("last inserted id =", lastInsertId)

        fmt.Println("# Updating")
        stmt, err := db.Prepare("update userinfo set username=$1 where uid=$2")
        checkErr(err)

        res, err := stmt.Exec("astaxieupdate", lastInsertId)
        checkErr(err)

        affect, err := res.RowsAffected()
        checkErr(err)

        fmt.Println(affect, "rows changed")

        fmt.Println("# Querying")
        rows, err := db.Query("SELECT * FROM userinfo")
        checkErr(err)

        for rows.Next() {
            var uid int
            var username string
            var department string
            var created time.Time
            err = rows.Scan(&uid, &username, &department, &created)
            checkErr(err)
            fmt.Println("uid | username | department | created ")
            fmt.Printf("%3v | %8v | %6v | %6v\n", uid, username, department, created)
        }

        fmt.Println("# Deleting")
        stmt, err = db.Prepare("delete from userinfo where uid=$1")
        checkErr(err)

        res, err = stmt.Exec(lastInsertId)
        checkErr(err)

        affect, err = res.RowsAffected()
        checkErr(err)

        fmt.Println(affect, "rows changed")
    }

    func checkErr(err error) {
        if err != nil {
            panic(err)
        }
    }

Note that PostgreSQL uses the $1, $2 format instead of the ? that MySQL uses, and it has a different DSN format in sql.Open. Another thing is that the PostgreSQL driver does not support sql.Result.LastInsertId(). So instead of this,

    stmt, err := db.Prepare("INSERT INTO userinfo(username,departname,created) VALUES($1,$2,$3);")
    res, err := stmt.Exec("astaxie", "研发部门", "2012-12-09")
    fmt.Println(res.LastInsertId())

use db.QueryRow() and .Scan() to get the value for the last inserted id.

    err = db.QueryRow("INSERT INTO TABLE_NAME values($1) returning uid;",    VALUE1").Scan(&lastInsertId)
    fmt.Println(lastInsertId)

results matching ""

    No results matching ""