Documentation ¶
Overview ¶
Package pq is a pure Go Postgres driver for the database/sql package.
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 ParseMap(m map[string]interface{}) (string, error)
- func ParseURL(url string) (string, error)
- type Error
- type ErrorCode
- type NullTime
- 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
var TrafficLogging bool = false
Functions ¶
func CopyInSchema ¶
CopyInSchema creates COPY FROM statement that can be prepared with DB.Prepare().
func ParseURL ¶
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
type ErrorCode ¶
type ErrorCode string
ErrorCode is a five digit pq error code
func (ErrorCode) Name ¶
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 ¶
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 NullTimeNow ¶
func NullTimeNow() NullTime
Creates a new valid NullTime, containing the current time