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)
fallback_application_name - An application_name to fall back to if one isn't provided.
connect_timeout - Maximum wait for connection, in seconds. Zero or
not specified means wait indefinitely.
sslcert - Cert file location. The file must contain PEM encoded data.
sslkey - Key file location. The file must contain PEM encoded data.
sslrootcert - The location of the root certificate file. The file
must contain PEM encoded data.
Valid values for sslmode are:
disable - No SSL
require - Always SSL (skip verification)
verify-ca - Always SSL (verify that the certificate presented by the
server was signed by a trusted CA)
verify-full - Always SSL (verify that the certification presented by
the server was signed by a trusted CA and the server host name
matches the one in the certificate)
Use single quotes for values that contain whitespace:
"user=pqgotest password='with spaces'"
A backslash will escape the next character in values:
"user=space\ man password='it\'s valid'"
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.
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.
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:
var userid int
err := db.QueryRow(`INSERT INTO users(name, favorite_fruit, age)
VALUES('beatrice', 'starfruit', 93) RETURNING id`).Scan(&userid)
For more details on RETURNING, see the Postgres documentation:
Parameters pass through driver.DefaultParameterConverter before they are handled
by this package. When the binary_parameters connection option is enabled,
[]byte values are sent directly to the backend as data in binary format.
This package returns the following types for values from the PostgreSQL backend:
integer types smallint, integer, and bigint are returned as int64
floating-point types real and double precision are returned as float64
character types char, varchar, and text are returned as string
temporal types date, time, timetz, timestamp, and timestamptz are
returned as time.Time
the boolean type is returned as bool
the bytea type is returned as []byte
All other types are returned directly from the backend as []byte values in text format.
You can perform bulk imports by preparing a statement returned by pq.CopyIn (or
pq.CopyInSchema) in an explicit transaction (sql.Tx). The returned statement
handle can then be repeatedly "executed" to copy data into the target table.
After all data has been processed you should call Exec() once with no arguments
to flush all buffered data. Any call to Exec() might return an error which
should be handled appropriately, but because of the internal buffering an error
returned by Exec() might not be related to the data passed in the call that
failed.
CopyIn uses COPY FROM internally. It is not possible to COPY outside of an
explicit transaction in pq.
Usage example:
txn, err := db.Begin()
if err != nil {
log.Fatal(err)
stmt, err := txn.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)
err = txn.Commit()
if err != nil {
log.Fatal(err)
To start listening for notifications, you first have to open a new connection
to the database by calling NewListener. This connection can not be used for
anything other than LISTEN / NOTIFY. Calling Listen will open a "notification
channel"; once a notification channel is open, a notification generated on that
channel will effect a send on the Listener.Notify channel. A notification
channel will remain open until Unlisten is called, though connection loss might
result in some notifications being lost. To solve this problem, Listener sends
a nil pointer over the Notify channel any time the connection is re-established
following a connection loss. The application can get information about the
state of the underlying connection by setting an event callback in the call to
NewListener.
A single Listener can safely be used from concurrent goroutines, which means
that there is often no need to create more than one Listener in your
application. However, a Listener is always connected to a single database, so
you will need to create a new Listener instance for every database you want to
receive notifications in.
This package is in a separate module so that users who don't need Kerberos
don't have to download unnecessary dependencies.
When imported, additional connection string parameters are supported:
krbsrvname - GSS (Kerberos) service name when constructing the
SPN (default is `postgres`). This will be combined with the host
to form the full SPN: `krbsrvname/host`.
krbspn - GSS (Kerberos) SPN. This takes priority over
`krbsrvname` if present.
var (
ErrNotSupported = errors.New("pq: Unsupported command")
ErrInFailedTransaction = errors.New("pq: Could not complete operation in a failed transaction")
ErrSSLNotSupported = errors.New("pq: SSL is not enabled on the server")
ErrSSLKeyUnknownOwnership = errors.New("pq: Could not get owner information for private key, may not be properly protected")
ErrSSLKeyHasWorldPermissions = errors.New("pq: Private key has world access. Permissions should be u=rw,g=r (0640) if owned by root, or u=rw (0600), or less")
ErrCouldNotDetectUsername = errors.New("pq: Could not detect default username. Please provide one explicitly")
Array returns the optimal driver.Valuer and sql.Scanner for an array or
slice of any dimension.
For example:
db.Query(`SELECT * FROM t WHERE id = ANY($1)`, pq.Array([]int{235, 401}))
var x []sql.NullInt64
db.QueryRow(`SELECT ARRAY[235, 401]`).Scan(pq.Array(&x))
Scanning multi-dimensional arrays is not supported. Arrays where the lower
bound is not one (such as `[0:0]={1}') are not supported.
ConnectorNoticeHandler returns the currently set notice handler, if any. If
the given connector is not a result of ConnectorWithNoticeHandler, nil is
returned.
ConnectorNotificationHandler returns the currently set notification handler, if any. If
the given connector is not a result of ConnectorWithNotificationHandler, nil is
returned.
EnableInfinityTs controls the handling of Postgres' "-infinity" and
"infinity" "timestamp"s.
If EnableInfinityTs is not called, "-infinity" and "infinity" will return
[]byte("-infinity") and []byte("infinity") respectively, and potentially
cause error "sql: Scan error on column index 0: unsupported driver -> Scan
pair: []uint8 -> *time.Time", when scanning into a time.Time value.
Once EnableInfinityTs has been called, all connections created using this
driver will decode Postgres' "-infinity" and "infinity" for "timestamp",
"timestamp with time zone" and "date" types to the predefined minimum and
maximum times, respectively. When encoding time.Time values, any time which
equals or precedes the predefined minimum time will be encoded to
"-infinity". Any values at or past the maximum time will similarly be
encoded to "infinity".
If EnableInfinityTs is called with negative >= positive, it will panic.
Calling EnableInfinityTs after a connection has been established results in
undefined behavior. If EnableInfinityTs is called more than once, it will
panic.
NoticeHandler returns the notice handler on the given connection, if any. A
runtime panic occurs if c is not a pq connection. This is rarely used
directly, use ConnectorNoticeHandler and ConnectorWithNoticeHandler instead.
Open opens a new connection to the database. dsn is a connection string.
Most users should only use it through database/sql package from the standard
library.
ParseTimestamp parses Postgres' text format. It returns a time.Time in
currentLocation iff that time's offset agrees with the offset sent from the
Postgres server. Otherwise, ParseTimestamp returns a time.Time with the
fixed offset offset provided by the Postgres server.
QuoteIdentifier quotes an "identifier" (e.g. a table or a column name) to be
used as part of an SQL statement. For example:
tblname := "my_table"
data := "my_data"
quoted := pq.QuoteIdentifier(tblname)
err := db.Exec(fmt.Sprintf("INSERT INTO %s VALUES ($1)", quoted), data)
Any double quotes in name will be escaped. The quoted identifier will be
case sensitive when used in a query. If the input string contains a zero
byte, the result will be truncated immediately before it.
QuoteLiteral quotes a 'literal' (e.g. a parameter, often used to pass literal
to DDL and other statements that do not accept parameters) to be used as part
of an SQL statement. For example:
exp_date := pq.QuoteLiteral("2023-01-05 15:00:00Z")
err := db.Exec(fmt.Sprintf("CREATE ROLE my_user VALID UNTIL %s", exp_date))
Any single quotes in name will be escaped. Any backslashes (i.e. "\") will be
replaced by two backslashes (i.e. "\\") and the C-style escape identifier
that PostgreSQL provides ('E') will be prepended to the string.
RegisterGSSProvider registers a GSS authentication provider. For example, if
you need to use Kerberos to authenticate with your server, add this to your
main package:
SetNoticeHandler sets the given notice handler on the given connection. A
runtime panic occurs if c is not a pq connection. A nil handler may be used
to unset it. This is rarely used directly, use ConnectorNoticeHandler and
ConnectorWithNoticeHandler instead.
Note: Notice handlers are executed synchronously by pq meaning commands
won't continue to be processed until the handler returns.
SetNotificationHandler sets the given notification handler on the given
connection. A runtime panic occurs if c is not a pq connection. A nil handler
may be used to unset it.
Note: Notification handlers are executed synchronously by pq meaning commands
won't continue to be processed until the handler returns.
type ArrayDelimiter interface {
// ArrayDelimiter returns the delimiter character(s) for this element's type. ArrayDelimiter() string
ArrayDelimiter may be optionally implemented by driver.Valuer or sql.Scanner
to override the array delimiter used by GenericArray.
Value implements the driver.Valuer interface. It uses the "hex" format which
is only supported on PostgreSQL 9.0 or newer.
Connector represents a fixed configuration for the pq driver with a given
name. Connector satisfies the database/sql/driver Connector interface and
can be used to create any number of DB Conn's via the database/sql OpenDB
function.
NewConnector returns a connector for the pq driver in a fixed configuration
with the given dsn. The returned connector can be used to create any number
of equivalent Conn's. The returned connector is intended to be used with
database/sql.OpenDB.
Open opens a new connection to the database. name is a connection string.
Most users should only use it through database/sql package from the standard
library.
Name returns the condition name of an error class. It is equivalent to the
condition name of the "standard" error code (i.e. the one having the last
three characters "000").
GenericArray implements the driver.Valuer and sql.Scanner interfaces for
an array or slice of any dimension.
type Listener struct {
// Channel for receiving notifications from the database. In some cases a// nil value will be sent. See section "Notifications" above.
Notify chan *Notification// contains filtered or unexported fields
Listener provides an interface for listening to notifications from a
PostgreSQL database. For general usage information, see section
"Notifications".
NewListener creates a new database connection dedicated to LISTEN / NOTIFY.
name should be set to a connection string to be used to establish the
database connection (see section "Connection String Parameters" above).
minReconnectInterval controls the duration to wait before trying to
re-establish the database connection after connection loss. After each
consecutive failure this interval is doubled, until maxReconnectInterval is
reached. Successfully completing the connection establishment procedure
resets the interval back to minReconnectInterval.
The last parameter eventCallback can be set to a function which will be
called by the Listener when the state of the underlying database connection
changes. This callback will be called by the goroutine which dispatches the
notifications over the Notify channel, so you should try to avoid doing
potentially time-consuming operations from the callback.
Close disconnects the Listener from the database and shuts it down.
Subsequent calls to its methods will return an error. Close returns an
error if the connection has already been closed.
Listen starts listening for notifications on a channel. Calls to this
function will block until an acknowledgement has been received from the
server. Note that Listener automatically re-establishes the connection
after connection loss, so this function may block indefinitely if the
connection can not be re-established.
Listen will only fail in three conditions:
The channel is already open. The returned error will be
ErrChannelAlreadyOpen.
The query was executed on the remote server, but PostgreSQL returned an
error message in response to the query. The returned error will be a
pq.Error containing the information the server supplied.
Close is called on the Listener before the request could be completed.
NotificationChannel returns the notification channel for this listener.
This is the same channel as Notify, and will not be recreated during the
life time of the Listener.
Unlisten removes a channel from the Listener's channel list. Returns
ErrChannelNotOpen if the Listener is not listening on the specified channel.
Returns immediately with no error if there is no connection. Note that you
might still get notifications for this channel even after Unlisten has
returned.
UnlistenAll removes all channels from the Listener's channel list. Returns
immediately with no error if there is no connection. Note that you might
still get notifications for any of the deleted channels even after
UnlistenAll has returned.
ExecSimpleQuery executes a "simple query" (i.e. one with no bindable
parameters) on the connection. The possible return values are:
"executed" is true; the query was executed to completion on the
database server. If the query failed, err will be set to the error
returned by the database, otherwise err will be nil.
If "executed" is false, the query could not be executed on the remote
server. err will be non-nil.
After a call to ExecSimpleQuery has returned an executed=false value, the
connection has either been closed or will be closed shortly thereafter, and
all subsequently executed queries will return an error.
Ping the remote server to make sure it's alive. Non-nil error means the
connection has failed and should be abandoned.
const (
// ListenerEventConnected is emitted only when the database connection// has been initially initialized. The err argument of the callback// will always be nil.
ListenerEventConnected ListenerEventType = iota// ListenerEventDisconnected is emitted after a database connection has// been lost, either because of an error or because Close has been// called. The err argument will be set to the reason the database// connection was lost.
ListenerEventDisconnected
// ListenerEventReconnected is emitted after a database connection has// been re-established after connection loss. The err argument of the// callback will always be nil. After this event has been emitted, a// nil pq.Notification is sent on the Listener.Notify channel.
ListenerEventReconnected
// ListenerEventConnectionAttemptFailed is emitted after a connection// to the database was attempted, but failed. The err argument will be// set to an error describing why the connection attempt did not// succeed.
ListenerEventConnectionAttemptFailed
ConnectorWithNoticeHandler creates or sets the given handler for the given
connector. If the given connector is a result of calling this function
previously, it is simply set on the given connector and returned. Otherwise,
this returns a new connector wrapping the given one and setting the notice
handler. A nil notice handler may be used to unset it.
The returned connector is intended to be used with database/sql.OpenDB.
Note: Notice handlers are executed synchronously by pq meaning commands
won't continue to be processed until the handler returns.
Example ¶
package main
import (
"database/sql"
"fmt"
"log"
"github.com/lib/pq"
func main() {
name := ""
// Base connector to wrap
base, err := pq.NewConnector(name)
if err != nil {
log.Fatal(err)
// Wrap the connector to simply print out the message
connector := pq.ConnectorWithNoticeHandler(base, func(notice *pq.Error) {
fmt.Println("Notice sent: " + notice.Message)
db := sql.OpenDB(connector)
defer db.Close()
// Raise a notice
sql := "DO language plpgsql $$ BEGIN RAISE NOTICE 'test notice'; END $$"
if _, err := db.Exec(sql); err != nil {
log.Fatal(err)
Connect calls the underlying connector's connect method and then sets the
notice handler.
type Notification struct {
// Process ID (PID) of the notifying postgres backend. BePid int// Name of the channel the notification was sent on. Channel string// Payload, or the empty string if unspecified. Extra string
Notification represents a single notification from the database.
type NotificationHandlerConnector struct {
driver.Connector// contains filtered or unexported fields
NotificationHandlerConnector wraps a regular connector and sets a notification handler
on it.
ConnectorWithNotificationHandler creates or sets the given handler for the given
connector. If the given connector is a result of calling this function
previously, it is simply set on the given connector and returned. Otherwise,
this returns a new connector wrapping the given one and setting the notification
handler. A nil notification handler may be used to unset it.
The returned connector is intended to be used with database/sql.OpenDB.
Note: Notification handlers are executed synchronously by pq meaning commands
won't continue to be processed until the handler returns.
Connect calls the underlying connector's connect method and then sets the
notification handler.
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.
Package listen is a self-contained Go program which uses the LISTEN / NOTIFY mechanism to avoid polling the database while waiting for more work to arrive.
Package listen is a self-contained Go program which uses the LISTEN / NOTIFY mechanism to avoid polling the database while waiting for more work to arrive.
go.dev uses cookies from Google to deliver and enhance the quality of its services and to
analyze traffic. Learn more.
