CopyIn creates a COPY FROM statement which can be prepared with Tx.Prepare(). The target table should be visible in search_path.

It copies all columns if the list of columns is empty.

Deprecated: there is no need to use this query builder, you can use:

tx.Prepare("copy tbl (col1, col2) from stdin")

CopyInSchema creates a COPY FROM statement which can be prepared with Tx.Prepare().

Deprecated: there is no need to use this query builder, you can use:

tx.Prepare("copy schema.tbl (col1, col2) from stdin")

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

Deprecated: directly passing an URL to sql.Open("postgres", "postgres://...") now works, and calling this manually is no longer required.

func RegisterGSSProvider(newGssArg NewGSSFunc)

package main
import (
	"crypto/tls"
	"crypto/x509"
	"database/sql"
	"log"
	"os"
	"github.com/lib/pq"
)
func main() {
	pem, err := os.ReadFile("testdata/ssl/root.crt")
	if err != nil {
		log.Fatal(err)
	}
	root := x509.NewCertPool()
	root.AppendCertsFromPEM(pem)
	certs, err := tls.LoadX509KeyPair("testdata/ssl/postgresql.crt", "testdata/ssl/postgresql.key")
	if err != nil {
		log.Fatal(err)
	}
	pq.RegisterTLSConfig("mytls", &tls.Config{
		RootCAs:      root,
		Certificates: []tls.Certificate{certs},
		ServerName:   "postgres",
	})
	db, err := sql.Open("postgres", "host=postgres dbname=pqgo sslmode=pqgo-mytls")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()
	err = db.Ping()
	if err != nil {
		log.Fatal(err)
	}
}

func SetNoticeHandler(c driver.Conn, handler func(*Error))

func SetNotificationHandler(c driver.Conn, handler func(*Notification))

type ArrayDelimiter interface {

	ArrayDelimiter() string
}

Scan implements the sql.Scanner interface.

Value implements the driver.Valuer interface.

Scan implements the sql.Scanner interface.

Value implements the driver.Valuer interface. It uses the "hex" format which is only supported on PostgreSQL 9.0 or newer.

type Config struct {


	Host string `postgres:"host" env:"PGHOST"`


	Hostaddr netip.Addr `postgres:"hostaddr" env:"PGHOSTADDR"`


	Port uint16 `postgres:"port" env:"PGPORT"`

	Database string `postgres:"dbname" env:"PGDATABASE"`

	User string `postgres:"user" env:"PGUSER"`

	Password string `postgres:"password" env:"PGPASSWORD"`


	Passfile string `postgres:"passfile" env:"PGPASSFILE"`

	Options string `postgres:"options" env:"PGOPTIONS"`

	ApplicationName string `postgres:"application_name" env:"PGAPPNAME"`


	FallbackApplicationName string `postgres:"fallback_application_name" env:"-"`


	SSLMode SSLMode `postgres:"sslmode" env:"PGSSLMODE"`

	SSLNegotiation SSLNegotiation `postgres:"sslnegotiation" env:"PGSSLNEGOTIATION"`


	SSLCert string `postgres:"sslcert" env:"PGSSLCERT"`


	SSLKey string `postgres:"sslkey" env:"PGSSLKEY"`


	SSLRootCert string `postgres:"sslrootcert" env:"PGSSLROOTCERT"`


	SSLSNI bool `postgres:"sslsni" env:"PGSSLSNI"`


	SSLMinProtocolVersion SSLProtocolVersion `postgres:"ssl_min_protocol_version" env:"SSLPGMINPROTOCOLVERSION"`


	SSLMaxProtocolVersion SSLProtocolVersion `postgres:"ssl_max_protocol_version" env:"SSLPGMAXPROTOCOLVERSION"`


	SSLInline bool `postgres:"sslinline" env:"-"`


	KrbSrvname string `postgres:"krbsrvname" env:"PGKRBSRVNAME"`


	KrbSpn string `postgres:"krbspn" env:"-"`


	ConnectTimeout time.Duration `postgres:"connect_timeout" env:"PGCONNECT_TIMEOUT"`


	BinaryParameters bool `postgres:"binary_parameters" env:"-"`


	DisablePreparedBinaryResult bool `postgres:"disable_prepared_binary_result" env:"-"`

	ClientEncoding string `postgres:"client_encoding" env:"PGCLIENTENCODING"`


	Datestyle string `postgres:"datestyle" env:"PGDATESTYLE"`

	TZ string `postgres:"tz" env:"PGTZ"`

	Geqo string `postgres:"geqo" env:"PGGEQO"`


	TargetSessionAttrs TargetSessionAttrs `postgres:"target_session_attrs" env:"PGTARGETSESSIONATTRS"`


	LoadBalanceHosts LoadBalanceHosts `postgres:"load_balance_hosts" env:"PGLOADBALANCEHOSTS"`


	MinProtocolVersion ProtocolVersion `postgres:"min_protocol_version" env:"PGMINPROTOCOLVERSION"`

	MaxProtocolVersion ProtocolVersion `postgres:"max_protocol_version" env:"PGMAXPROTOCOLVERSION"`


	Service string `postgres:"service" env:"PGSERVICE"`

	ServiceFile string `postgres:"-" env:"PGSERVICEFILE"`


	Runtime map[string]string `postgres:"-" env:"-"`


	Multi []ConfigMultihost
}

NewConfig creates a new Config from the defaults, environment, service file, and DSN, in that order. That is: a service overrides any value from the environment, which in turn gets overridden by the same parameter in the connection string.

Most connection parameters supported by PostgreSQL are supported; see the Config struct for supported parameters. pq also lets you specify any run-time parameter such as search_path or work_mem in the connection string. This is different from libpq, which uses the "options" parameter for this (which also works in pq).

key=value connection strings ¶

For key=value strings, use single quotes for values that contain whitespace or empty values. A backslash will escape the next character:

"user=pqgo password='with spaces'"
"user=''"
"user=space\ man password='it\'s valid'"

URL connection strings ¶

pq supports URL-style postgres:// or postgresql:// connection strings in the form:

postgres[ql]://[user[:pwd]@][net-location][:port][/dbname][?param1=value1&...]

Go's net/url.Parse is more strict than PostgreSQL's URL parser and will (correctly) reject %2F in the host part. This means that unix-socket URLs:

postgres://[user[:pwd]@][unix-socket][:port[/dbname]][?param1=value1&...]
postgres://%2Ftmp%2Fpostgres/db

will not work. You will need to use "host=/tmp/postgres dbname=db".

Similarly, multiple ports also won't work, but ?port= will:

postgres://host1,host2:5432,6543/dbname         Doesn't work
postgres://host1,host2/dbname?port=5432,6543    Works

Environment ¶

Most PostgreSQL environment variables are supported by pq. Environment variables have a lower precedence than explicitly provided connection parameters. pq will return an error if environment variables it does not support are set. Environment variables have a lower precedence than explicitly provided connection parameters.

func (cfg Config) Clone() Config

Clone returns a copy of the Config.

type Connector struct {
}

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 sql.OpenDB.

package main
import (
	"database/sql"
	"log"
	"github.com/lib/pq"
)
func main() {
	c, err := pq.NewConnector("host=postgres dbname=pqgo")
	if err != nil {
		log.Fatal(err)
	}
	db := sql.OpenDB(c)
	defer db.Close()
	err = db.Ping()
	if err != nil {
		log.Fatal(err)
	}
}

func NewConnectorConfig(cfg Config) (*Connector, error)

NewConnectorConfig returns a connector for the pq driver in a fixed configuration with the given Config. The returned connector can be used to create any number of equivalent Conn's. The returned connector is intended to be used with sql.OpenDB.

Connect returns a connection to the database using the fixed configuration of this Connector. Context is not used.

func (c *Connector) Dialer(dialer Dialer)

Dialer allows change the dialer used to open connections.

Driver returns the underlying driver of this Connector.

type Driver struct{}

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.

As asserts that the given error is pq.Error and returns it, returning nil if it's not a pq.Error.

It will return nil if the pq.Error is not one of the given error codes. If no codes are given it will always return the Error.

This is safe to call with a nil error.

package main
import (
	"database/sql"
	"log"
	"github.com/lib/pq"
	"github.com/lib/pq/pqerror"
)
func main() {
	db, err := sql.Open("postgres", "")
	if err != nil {
		log.Fatal(err)
	}
	email := "hello@example.com"
	_, err = db.Exec("insert into t (email) values ($1)", email)
	if pqErr := pq.As(err, pqerror.UniqueViolation); pqErr != nil {
		log.Fatalf("email %q already exsts", email)
	}
	if err != nil {
		log.Fatalf("unknown error: %s", err)
	}
}

func (e *Error) ErrorWithDetail() string

ErrorWithDetail returns the error message with detailed information and location context (if any).

See the documentation on Error.

func (e *Error) Fatal() bool

Fatal returns true if the Error Severity is fatal.

SQLState returns the SQLState of the error.

ErrorClass is only the class part of an error code.

Deprecated: use pqerror.Class

ErrorCode is a five-character error code.

Deprecated: use pqerror.Code

type EventCallbackType func(event ListenerEventType, err error)

Scan implements the sql.Scanner interface.

Value implements the driver.Valuer interface.

Scan implements the sql.Scanner interface.

Value implements the driver.Valuer interface.

type GenericArray struct{ A any }

Scan implements the sql.Scanner interface.

Value implements the driver.Valuer interface.

Scan implements the sql.Scanner interface.

Value implements the driver.Valuer interface.

Scan implements the sql.Scanner interface.

Value implements the driver.Valuer interface.

type Listener struct {


	Notify chan *Notification
}

package main
import (
	"database/sql"
	"fmt"
	"log"
	"time"
	"github.com/lib/pq"
)
func main() {
	// Connect with Listener.
	var (
		dsn          = "dbname=pqgo "
		minReconnect = 10 * time.Second
		maxReconnect = time.Minute
	)
	l := pq.NewListener(dsn, minReconnect, maxReconnect, func(ev pq.ListenerEventType, err error) {
		fmt.Printf("callback: %s: %v\n", ev, err)
	})
	defer l.Close()
	// Can listen on as many channels as you want.
	err := l.Listen("coconut")
	if err != nil {
		log.Fatal(err)
	}
	err = l.Listen("banana")
	if err != nil {
		log.Fatal(err)
	}
	// Send notifications for our test.
	go func() {
		db, err := sql.Open("postgres", dsn)
		if err != nil {
			log.Fatal(err)
		}
		defer db.Close()
		_, err = db.Exec(`notify coconut, 'got a lovely bunch'`)
		if err != nil {
			log.Fatal(err)
		}
		_, err = db.Exec(`notify banana, 'yellow and curvy'`)
		if err != nil {
			log.Fatal(err)
		}
	}()
	// Keep listening on Notify channel.
	var i int
	for {
		select {
		case <-time.After(1 * time.Second):
			l.Close()
		case n := <-l.Notify:
			i++
			if n == nil {
				fmt.Println("nil notify: closing Listener")
				return
			}
			fmt.Printf("notification on %q with data %q\n", n.Channel, n.Extra)
			// Quickly exit after second notification in this example, so tests
			// run faster.
			if i == 2 {
				l.Close()
			}
		}
	}
}

Output:
callback: connected: <nil>
notification on "coconut" with data "got a lovely bunch"
notification on "banana" with data "yellow and curvy"
nil notify: closing Listener

NewDialListener is like NewListener but it takes a Dialer.

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).

minReconnect 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 maxReconnect is reached. Successfully completing the connection establishment procedure resets the interval back to minReconnect.

The last parameter cb 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:

1. The channel is already open. The returned error will be ErrChannelAlreadyOpen.
2. 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.
3. Close is called on the Listener before the request could be completed.

The channel name is case-sensitive.

func (l *Listener) NotificationChannel() <-chan *Notification

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.

Ping the remote server to make sure it's alive. Non-nil return value means that there is no active connection.

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.

The channel name is case-sensitive.

func (l *Listener) UnlistenAll() error

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.

type ListenerConn struct {
}

func NewListenerConn(name string, notificationChan chan<- *Notification) (*ListenerConn, error)

NewListenerConn creates a new ListenerConn. Use NewListener instead.

Close closes the connection.

Err returns the reason the connection was closed. It is not safe to call this function until l.Notify has been closed.

ExecSimpleQuery executes a "simple query" (i.e. one with no bindable parameters) on the connection. The possible return values are:

1. "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.
2. 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.

Listen sends a LISTEN query to the server. See ExecSimpleQuery.

Ping the remote server to make sure it's alive. Non-nil error means the connection has failed and should be abandoned.

Unlisten sends an UNLISTEN query to the server. See ExecSimpleQuery.

UnlistenAll sends an `UNLISTEN *` query to the server. See ExecSimpleQuery.

type ListenerEventType int

const (


	ListenerEventConnected ListenerEventType = iota


	ListenerEventDisconnected


	ListenerEventReconnected


	ListenerEventConnectionAttemptFailed
)

type NewGSSFunc func() (GSS, error)

func ConnectorWithNoticeHandler ¶ added in v1.4.0

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.

package main
import (
	"database/sql"
	"fmt"
	"log"
	"github.com/lib/pq"
)
func main() {
	// Base connector to wrap
	base, err := pq.NewConnector("dbname=pqgo")
	if err != nil {
		log.Fatal(err)
	}
	// Wrap the connector to simply print out the message
	connector := pq.ConnectorWithNoticeHandler(base, func(notice *pq.Error) {
		fmt.Printf("NOTICE: %s\n", notice.Message)
	})
	db := sql.OpenDB(connector)
	defer db.Close()
	// Raise a notice
	_, err = db.Exec(`drop table if exists doesntexist`)
	if err != nil {
		log.Fatal(err)
	}
	// And via PL/pgSQL.
	_, err = db.Exec(`
		do language plpgsql $$ begin
			raise notice 'test notice';
		end $$
	`)
	if err != nil {
		log.Fatal(err)
	}
}

Output:
NOTICE: table "doesntexist" does not exist, skipping
NOTICE: test notice

func (*NoticeHandlerConnector) Connect ¶ added in v1.4.0

Connect calls the underlying connector's connect method and then sets the notice handler.

type Notification struct {
	BePid   int
	Channel string
}

func ConnectorWithNotificationHandler ¶ added in v1.5.1

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.

func (*NotificationHandlerConnector) Connect ¶ added in v1.5.1

Connect calls the underlying connector's connect method and then sets the notification handler.

PGError is an interface used by previous versions of pq.

Deprecated: use the Error type. This is never used.

type SSLProtocolVersion string

Scan implements the sql.Scanner interface.

Value implements the driver.Valuer interface.

type TargetSessionAttrs string