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:

  1.     CREATE TABLE userinfo
  2.     (
  3.         uid serial NOT NULL,
  4.         username character varying(100) NOT NULL,
  5.         department character varying(500) NOT NULL,
  6.         Created date,
  7.         CONSTRAINT userinfo_pkey PRIMARY KEY (uid)
  8.     )
  9.     WITH (OIDS=FALSE);

An example:

  1.     package main
  3.     import (
  4.         "database/sql"
  5.         "fmt"
  6.         _ "github.com/lib/pq"
  7.         "time"
  8.     )
  10.     const (
  11.         DB_USER     = "postgres"
  12.         DB_PASSWORD = "postgres"
  13.         DB_NAME     = "test"
  14.     )
  16.     func main() {
  17.         dbinfo := fmt.Sprintf("user=%s password=%s dbname=%s sslmode=disable",
  18.             DB_USER, DB_PASSWORD, DB_NAME)
  19.         db, err := sql.Open("postgres", dbinfo)
  20.         checkErr(err)
  21.         defer db.Close()
  23.         fmt.Println("# Inserting values")
  25.         var lastInsertId int
  26.         err = db.QueryRow("INSERT INTO userinfo(username,department,created) VALUES($1,$2,$3) returning uid;", "astaxie", "研发部门", "2012-12-09").Scan(&lastInsertId)
  27.         checkErr(err)
  28.         fmt.Println("last inserted id =", lastInsertId)
  30.         fmt.Println("# Updating")
  31.         stmt, err := db.Prepare("update userinfo set username=$1 where uid=$2")
  32.         checkErr(err)
  34.         res, err := stmt.Exec("astaxieupdate", lastInsertId)
  35.         checkErr(err)
  37.         affect, err := res.RowsAffected()
  38.         checkErr(err)
  40.         fmt.Println(affect, "rows changed")
  42.         fmt.Println("# Querying")
  43.         rows, err := db.Query("SELECT * FROM userinfo")
  44.         checkErr(err)
  46.         for rows.Next() {
  47.             var uid int
  48.             var username string
  49.             var department string
  50.             var created time.Time
  51.             err = rows.Scan(&uid, &username, &department, &created)
  52.             checkErr(err)
  53.             fmt.Println("uid | username | department | created ")
  54.             fmt.Printf("%3v | %8v | %6v | %6v\n", uid, username, department, created)
  55.         }
  57.         fmt.Println("# Deleting")
  58.         stmt, err = db.Prepare("delete from userinfo where uid=$1")
  59.         checkErr(err)
  61.         res, err = stmt.Exec(lastInsertId)
  62.         checkErr(err)
  64.         affect, err = res.RowsAffected()
  65.         checkErr(err)
  67.         fmt.Println(affect, "rows changed")
  68.     }
  70.     func checkErr(err error) {
  71.         if err != nil {
  72.             panic(err)
  73.         }
  74.     }

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,

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

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

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