pq

README

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

Install

go get github.com/adjust/pq

Docs

http://godoc.org/github.com/adjust/pq

Use

package main

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

func main() {
	db, err := sql.Open("postgres", "user=pqgotest dbname=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.

Most environment variables 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.

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.

See http://www.postgresql.org/docs/current/static/libpq-connect.html.

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 are:
  • disable - No SSL
  • require - Always SSL (skip verification)
  • verify-full - Always SSL (require verification)

Use single quotes for values that contain whitespace:

"user=pqgotest password='with spaces'"

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.

See http://golang.org/pkg/database/sql to learn how to use with pq through the database/sql package.

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/adjust/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)
• 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
• hstore sugar (i.e. handling hstore in rows.Scan)

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)
• 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 Robenolt (mattrobenolt)
• Martin Olsen (martinolsen)
• Mike Lewis (mikelikespie)
• Nicolas Patry (Narsil)
• 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/adjust/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")

Note that 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)

Note also that 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

Index

• Constants
• Variables
• 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")
)

Common error types

Functions

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.