pq

README

pq - A pure Go postgres driver for Go's database/sql package

Install

go get github.com/lib/pq

Docs

For detailed documentation and basic usage examples, please see the package documentation at http://godoc.org/github.com/lib/pq.

Tests

go test is used for testing. A running PostgreSQL server is required, with the ability to log in. The default database to connect to test with is "pqgotest," but it can be overridden using environment variables.

Example:

PGHOST=/var/run/postgresql go test github.com/lib/pq

Optionally, a benchmark suite can be run as part of the tests:

PGHOST=/var/run/postgresql go test -bench .

Features

• SSL
• Handles bad connections for database/sql
• Scan time.Time correctly (i.e. timestamp[tz], time[tz], date)
• Scan binary blobs correctly (i.e. bytea)
• Package for hstore support
• COPY FROM support
• pq.ParseURL for converting urls to connection strings for sql.Open.
• Many libpq compatible environment variables
• Unix socket support

Future / Things you can help with

• Notifications: LISTEN/NOTIFY (see #156)
• Better COPY FROM / COPY TO (see discussion in #181)

Thank you (alphabetical)

Some of these contributors are from the original library bmizerany/pq.go whose code still exists in here.

• Andy Balholm (andybalholm)
• Ben Berkert (benburkert)
• Bill Mill (llimllib)
• Bjørn Madsen (aeons)
• Blake Gentry (bgentry)
• Brad Fitzpatrick (bradfitz)
• Chris Walsh (cwds)
• Daniel Farina (fdr)
• Everyone at The Go Team
• Evan Shaw (edsrzf)
• Ewan Chou (coocood)
• Federico Romero (federomero)
• Gary Burd (garyburd)
• Heroku (heroku)
• Jason McVetta (jmcvetta)
• Jeremy Jay (pbnjay)
• Joakim Sernbrant (serbaut)
• John Gallagher (jgallagher)
• Kamil Kisiel (kisielk)
• Kelly Dunn (kellydunn)
• Keith Rarick (kr)
• Maciek Sakrejda (deafbybeheading)
• Marc Brinkmann (mbr)
• Marko Tiikkaja (johto)
• Matt Newberry (MattNewberry)
• Matt Robenolt (mattrobenolt)
• Martin Olsen (martinolsen)
• Mike Lewis (mikelikespie)
• Nicolas Patry (Narsil)
• Oliver Tonnhofer (olt)
• Paul Hammond (paulhammond)
• Ryan Smith (ryandotsmith)
• Samuel Stauffer (samuel)
• notedit (notedit)

Documentation

Overview

Package pq is a pure Go Postgres driver for the database/sql package.

In most cases clients will use the database/sql package instead of using this package directly. For example:

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

func main() {
	db, err := sql.Open("postgres", "user=pqgotest dbname=pqgotest sslmode=verify-full")
	if err != nil {
		log.Fatal(err)
	}

	age := 21
	rows, err := db.Query("SELECT name FROM users WHERE age = $1", age)
	…
}

You can also connect to a database using a URL. For example:

db, err := sql.Open("postgres", "postgres://pqgotest:password@localhost/pqgotest?sslmode=verify-full")

Connection String Parameters

Similarly to libpq, when establishing a connection using pq you are expected to supply a connection string containing zero or more parameters. A subset of the connection parameters supported by libpq are also supported by pq. Additionally, pq also lets you specify run-time parameters (such as search_path or work_mem) directly in the connection string. This is different from libpq, which does not allow run-time parameters in the connection string, instead requiring you to supply them in the options parameter.

For compatibility with libpq, the following special connection parameters are supported:

• dbname - The name of the database to connect to
• user - The user to sign in as
• password - The user's password
• host - The host to connect to. Values that start with / are for unix domain sockets. (default is localhost)
• port - The port to bind to. (default is 5432)
• sslmode - Whether or not to use SSL (default is require, this is not the default for libpq)

Valid values for sslmode are:

• disable - No SSL
• require - Always SSL (skip verification)
• verify-full - Always SSL (require verification)

See http://www.postgresql.org/docs/current/static/libpq-connect.html#LIBPQ-CONNSTRING for more information about connection string parameters.

Use single quotes for values that contain whitespace:

"user=pqgotest password='with spaces'"

Note that the connection parameter client_encoding (which sets the text encoding for the connection) may be set but must be "UTF8", matching with the same rules as Postgres. It is an error to provide any other value.

In addition to the parameters listed above, any run-time parameter that can be set at backend start time can be set in the connection string. For more information, see http://www.postgresql.org/docs/current/static/runtime-config.html.

Most environment variables as specified at http://www.postgresql.org/docs/current/static/libpq-envars.html supported by libpq are also supported by pq. If any of the environment variables not supported by pq are set, pq will panic during connection establishment. Environment variables have a lower precedence than explicitly provided connection parameters.

Queries

database/sql does not dictate any specific format for parameter markers in query strings, and pq uses the Postgres-native ordinal markers, as shown above. The same marker can be reused for the same parameter:

rows, err := db.Query(`SELECT name FROM users WHERE favorite_fruit = $1
	OR age BETWEEN $2 AND $2 + 3`, "orange", 64)

pq does not support the LastInsertId() method of the Result type in database/sql. To return the identifier of an INSERT (or UPDATE or DELETE), use the Postgres RETURNING clause with a standard Query or QueryRow call:

rows, err := db.Query(`INSERT INTO users(name, favorite_fruit, age)
	VALUES('beatrice', 'starfruit', 93) RETURNING id`)

For more details on RETURNING, see the Postgres documentation:

http://www.postgresql.org/docs/current/static/sql-insert.html
http://www.postgresql.org/docs/current/static/sql-update.html
http://www.postgresql.org/docs/current/static/sql-delete.html

For additional instructions on querying see the documentation for the database/sql package.

Errors

pq may return errors of type *pq.Error which can be interrogated for error details:

if err, ok := err.(*pq.Error), ok {
    fmt.Println("pq error:", err.Code.Name())
}

See the pq.Error type for details.

Bulk imports

You can make bulk imports by preparing a pq.CopyIn statement. pq.CopyIn uses Postgres COPY FROM feature. The encoding is handled by pq and you can insert rows by calling stmt.Exec. Note that bulk inserts are asynchronous and Exec can return errors for previous Exec calls. It is also necessary to call stmt.Exec() before stmt.Close() to get any errors from pending inserts. For example:

stmt, err := db.Prepare(pq.CopyIn("users", "name", "age"))
if err != nil {
	log.Fatal(err)
}

for _, user := range users {
	_, err = stmt.Exec(user.Name, int64(user.Age))
	if err != nil {
		log.Fatal(err)
	}
}

_, err = stmt.Exec()
if err != nil {
	log.Fatal(err)
}

err = stmt.Close()
if err != nil {
	log.Fatal(err)
}

Index

• Constants
• Variables
• func CopyIn(table string, columns ...string) string
• func CopyInSchema(schema, table string, columns ...string) string
• func Open(name string) (_ driver.Conn, err error)
• func ParseURL(url string) (string, error)
• type Error
  • func (err Error) Error() string
  • func (err *Error) Fatal() bool
  • func (err *Error) Get(k byte) (v string)
• type ErrorCode
  • func (ec ErrorCode) Name() string
• type NullTime
  • func (nt *NullTime) Scan(value interface{}) error
  • func (nt NullTime) Value() (driver.Value, error)
• type PGError

Constants

const (
	Efatal   = "FATAL"
	Epanic   = "PANIC"
	Ewarning = "WARNING"
	Enotice  = "NOTICE"
	Edebug   = "DEBUG"
	Einfo    = "INFO"
	Elog     = "LOG"
)

Error severities

Variables

var (
	ErrSSLNotSupported     = errors.New("pq: SSL is not enabled on the server")
	ErrNotSupported        = errors.New("pq: Unsupported command")
	ErrInFailedTransaction = errors.New("pq: Could not complete operation in a failed transaction")
)

Common error types

Functions

func CopyIn

func CopyIn(table string, columns ...string) string

CopyIn creates COPY FROM statement that can be prepared with DB.Prepare().

func CopyInSchema

func CopyInSchema(schema, table string, columns ...string) string

CopyInSchema creates COPY FROM statement that can be prepared with DB.Prepare().

func Open

func Open(name string) (_ driver.Conn, err error)

func ParseURL

func ParseURL(url string) (string, error)

ParseURL no longer needs to be used by clients of this library since supplying a URL as a connection string to sql.Open() is now supported:

sql.Open("postgres", "postgres://bob:secret@1.2.3.4:5432/mydb?sslmode=verify-full")

It remains exported here for backwards-compatibility.

ParseURL converts a url to a connection string for driver.Open. Example:

"postgres://bob:secret@1.2.3.4:5432/mydb?sslmode=verify-full"

converts to:

"user=bob password=secret host=1.2.3.4 port=5432 dbname=mydb sslmode=verify-full"

A minimal example:

"postgres://"

This will be blank, causing driver.Open to use all of the defaults

Types

type Error

type Error struct {
	Severity         string
	Code             ErrorCode
	Message          string
	Detail           string
	Hint             string
	Position         string
	InternalPosition string
	InternalQuery    string
	Where            string
	Schema           string
	Table            string
	Column           string
	DataTypeName     string
	Constraint       string
	File             string
	Line             string
	Routine          string
}

Error represents an error communicating with the server.

See http://www.postgresql.org/docs/current/static/protocol-error-fields.html for details of the fields

func (Error) Error

func (err Error) Error() string

func (*Error) Fatal

func (err *Error) Fatal() bool

Fatal returns true if the Error Severity is fatal.

func (*Error) Get

func (err *Error) Get(k byte) (v string)

Get implements the legacy PGError interface. New code should use the fields of the Error struct directly.

type ErrorCode

type ErrorCode string

ErrorCode is a five digit pq error code

func (ErrorCode) Name

func (ec ErrorCode) Name() string

Name returns a more human friendly rendering of the error code

See http://www.postgresql.org/docs/9.3/static/errcodes-appendix.html for details

type NullTime

type NullTime struct {
	Time  time.Time
	Valid bool // Valid is true if Time is not NULL
}

NullTime represents a time.Time that may be null. NullTime implements the sql.Scanner interface so it can be used as a scan destination, similar to sql.NullString.

func (*NullTime) Scan

func (nt *NullTime) Scan(value interface{}) error

Scan implements the Scanner interface.

func (NullTime) Value

func (nt NullTime) Value() (driver.Value, error)

Value implements the driver Valuer interface.

type PGError

type PGError interface {
	Error() string
	Fatal() bool
	Get(k byte) (v string)
}

PGError is an interface used by previous versions of pq. It is provided only to support legacy code. New code should use the Error type.