pq

package module
v1.12.0 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Mar 18, 2026 License: MIT Imports: 42 Imported by: 54,440

README

pq is a Go PostgreSQL driver for database/sql.

All maintained versions of PostgreSQL are supported. Older versions may work, but this is not tested. API docs.

Connecting

Use the postgres driver name in the sql.Open() call:

package main

import (
    "database/sql"
    "log"

    _ "github.com/lib/pq" // To register the driver.
)

func main() {
    // Or as URL: postgresql://localhost/pqgo
    db, err := sql.Open("postgres", "host=localhost dbname=pqgo")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // db.Open() only creates a connection pool, and doesn't actually establish
    // a connection. To ensure the connection works you need to do *something*
    // with a connection.
    err = db.Ping()
    if err != nil {
        log.Fatal(err)
    }
}

You can also use the pq.Config struct:

cfg := pq.Config{
    Host: "localhost",
    Port: 5432,
    User: "pqgo",
}
// Or: create a new Config from the defaults, environment, and DSN.
// cfg, err := pq.NewConfig("host=postgres dbname=pqgo")
// if err != nil {
//     log.Fatal(err)
// }

c, err := pq.NewConnectorConfig(cfg)
if err != nil {
    log.Fatal(err)
}

// Create connection pool.
db := sql.OpenDB(c)
defer db.Close()

// Make sure it works.
err = db.Ping()
if err != nil {
    log.Fatal(err)
}

The DSN is identical to PostgreSQL's libpq; most parameters are supported and should behave identical. Both key=value and postgres:// URL-style connection strings are supported. See the doc comments on the Config struct for the full list and documentation.

The most notable difference is that you can use 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).

For example:

sql.Open("postgres", "dbname=pqgo work_mem=100kB search_path=xyz")

The libpq way (which also works in pq) is to use options='-c k=v' like so:

sql.Open("postgres", "dbname=pqgo options='-c work_mem=100kB -c search_path=xyz'")

Errors

Errors from PostgreSQL are returned as pq.Error; pq.As can be used to convert an error to pq.Error:

pqErr := pq.As(err, pqerror.UniqueViolation)
if pqErr != nil {
  return fmt.Errorf("email %q already exsts", email)
}

the Error() string contains the error message and code:

pq: duplicate key value violates unique constraint "users_lower_idx" (23505)

The ErrorWithDetail() string also contains the DETAIL and CONTEXT fields, if present. For example for the above error this helpfully contains the duplicate value:

ERROR:   duplicate key value violates unique constraint "users_lower_idx" (23505)
DETAIL:  Key (lower(email))=(a@example.com) already exists.

Or for an invalid syntax error like this:

pq: invalid input syntax for type json (22P02)

It contains the context where this error occurred:

ERROR:   invalid input syntax for type json (22P02)
DETAIL:  Token "asd" is invalid.
CONTEXT: line 5, column 8:

      3 | 'def',
      4 | 123,
      5 | 'foo', 'asd'::jsonb
                 ^

PostgreSQL features

Authentication

pq supports PASSWORD, MD5, and SCRAM-SHA256 authentication out of the box. If you need GSS/Kerberos authentication you'll need to import the auth/kerberos module: package:

import "github.com/lib/pq/auth/kerberos"

func init() {
	pq.RegisterGSSProvider(func() (pq.Gss, error) { return kerberos.NewGSS() })
}

This is in a separate module so that users who don't need Kerberos (i.e. most users) don't have to add unnecessary dependencies.

Reading a password file (pgpass) is also supported.

Bulk imports with COPY [..] FROM STDIN

You can perform bulk imports by preparing a COPY [..] FROM STDIN statement inside a transaction. The returned sql.Stmt can then be repeatedly executed to copy data. After all data has been processed you should call Exec() once with no arguments to flush all buffered data.

Further documentation and example.

NOTICE errors

PostgreSQL has "NOTICE" errors for informational messages. For example from the psql CLI:

pqgo=# drop table if exists doesnotexist;
NOTICE:  table "doesnotexist" does not exist, skipping
DROP TABLE

These errors are not returned because they're not really errors but, well, notices.

You can register a callback for these notices with ConnectorWithNoticeHandler

Using LISTEN/NOTIFY

With pq.Listener notifications are send on a channel. For example:

l := pq.NewListener("dbname=pqgo", time.Second, time.Minute, nil)
defer l.Close()

err := l.Listen("coconut")
if err != nil {
    log.Fatal(err)
}

for {
    n := <-l.Notify:
    if n == nil {
        fmt.Println("nil notify: closing Listener")
        return
    }
    fmt.Printf("notification on %q with data %q\n", n.Channel, n.Extra)
}

And you'll get a notification for every notify coconut.

See the API docs for a more complete example.

Caveats

LastInsertId

sql.Result.LastInsertId() is not supported, because the PostgreSQL protocol does not have this facility. Use insert [..] returning [cols] instead:

db.QueryRow(`insert into tbl [..] returning id_col`).Scan(..)
// Or multiple rows:
db.Query(`insert into tbl (row1), (row2) returning id_col`)

This will also work in SQLite and MariaDB with the same syntax. MS-SQL and Oracle have a similar facility (with a different syntax).

timestamps

For timestamps with a timezone (timestamptz/timestamp with time zone), pq uses the timezone configured in the server, as libpq. You can change this with timestamp=[..] in the connection string. It's generally recommended to use UTC.

For timestamps without a timezone (timestamp/timestamp without time zone), pq always uses time.FixedZone("", 0) as the timezone; the timestamp parameter has no effect here. This is intentionally not equal to time.UTC, as it's not a UTC time: it's a time without a timezone. Go's time package does not really support this concept, so this is the best we can do This will print +0000 twice (e.g. 2026-03-15 17:45:47 +0000 +0000; having a clearer name would have been better, but is not compatible change). See this comment for some options on how to deal with this.

Also see the examples for timestamptz and timestamp

bytea with copy

All []byte parameters are encoded as bytea when using copy [..] from stdin, which may result in errors for e.g. jsonb columns. The solution is to use a string instead of []byte. See #1023

Development

Running tests

Tests need to be run against a PostgreSQL database; you can use Docker compose to start one:

docker compose up -d

This starts the latest PostgreSQL; use docker compose up -d pg«v» to start a different version.

In addition, your /etc/hosts needs an entry:

127.0.0.1 postgres postgres-invalid

Or you can use any other PostgreSQL instance; see testdata/init/docker-entrypoint-initdb.d for the required setup. You can use the standard PG* environment variables to control the connection details; it uses the following defaults:

PGHOST=localhost
PGDATABASE=pqgo
PGUSER=pqgo
PGSSLMODE=disable
PGCONNECT_TIMEOUT=20

PQTEST_BINARY_PARAMETERS can be used to add binary_parameters=yes to all connection strings:

PQTEST_BINARY_PARAMETERS=1 go test

Tests can be run against pgbouncer with:

docker compose up -d pgbouncer pg18
PGPORT=6432 go test ./...

and pgpool with:

docker compose up -d pgpool pg18
PGPORT=7432 go test ./...

Protocol debug output

You can use PQGO_DEBUG=1 to make the driver print the communication with PostgreSQL to stderr; this works anywhere (test or applications) and can be useful to debug protocol problems.

For example:

% PQGO_DEBUG=1 go test -run TestSimpleQuery
CLIENT → Startup                 69  "\x00\x03\x00\x00database\x00pqgo\x00user [..]"
SERVER ← (R) AuthRequest          4  "\x00\x00\x00\x00"
SERVER ← (S) ParamStatus         19  "in_hot_standby\x00off\x00"
[..]
SERVER ← (Z) ReadyForQuery        1  "I"
         START conn.query
         START conn.simpleQuery
CLIENT → (Q) Query                9  "select 1\x00"
SERVER ← (T) RowDescription      29  "\x00\x01?column?\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x17\x00\x04\xff\xff\xff\xff\x00\x00"
SERVER ← (D) DataRow              7  "\x00\x01\x00\x00\x00\x011"
         END conn.simpleQuery
         END conn.query
SERVER ← (C) CommandComplete      9  "SELECT 1\x00"
SERVER ← (Z) ReadyForQuery        1  "I"
CLIENT → (X) Terminate            0  ""
PASS
ok      github.com/lib/pq       0.010s

Documentation

Overview

Package pq is a Go PostgreSQL driver for database/sql.

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

import (
	"database/sql"

	_ "github.com/lib/pq"
)

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

	age := 21
	rows, err := db.Query("select name from users where age = $1", age)
	// …
}

You can also connect with an URL: 

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

Connection String Parameters

See NewConfig.

Queries

database/sql does not dictate any specific format for parameter placeholders, and pq uses the PostgreSQL-native ordinal markers ($1, $2, etc.). The same placeholder can be used more than once: 

rows, err := db.Query(
	`select * from users where name = $1 or age between $2 and $2 + 3`,
	"Duck", 64)

pq does not support sql.Result.LastInsertId. Use the RETURNING clause with a Query or QueryRow call instead to return the identifier: 

row := db.QueryRow(`insert into users(name, age) values('Scrooge McDuck', 93) returning id`)

var userid int
err := row.Scan(&userid)

Data Types

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.

Errors

pq may return errors of type *pq.Error which contain error details: 

pqErr := new(pq.Error)
if errors.As(err, &pqErr) {
    fmt.Println("pq error:", pqErr.Code.Name())
}

Bulk imports

You can perform bulk imports by preparing a "COPY [..] FROM STDIN" statement in a transaction (sql.Tx). The returned sql.Stmt 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.

It is not possible to COPY outside of an explicit transaction in pq.

Use nil for NULL, or explicitly add WITH NULL 'SOME STRING' (the default of \N doesn't work).

Notifications

PostgreSQL supports a simple publish/subscribe model using PostgreSQL's NOTIFY mechanism.

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.

The channel name in both Listen and Unlisten is case sensitive, and can contain any characters legal in an identifier. Note that the channel name will be truncated to 63 bytes by the PostgreSQL server.

Kerberos Support

If you need support for Kerberos authentication, add the following to your main package: 

import "github.com/lib/pq/auth/kerberos"

func init() {
	pq.RegisterGSSProvider(func() (pq.Gss, error) { return kerberos.NewGSS() })
}

This package is in a separate module so that users who don't need Kerberos don't have to add unnecessary dependencies.

Example (CopyFromStdin) 
package main

import (
	"database/sql"
	"fmt"
	"log"
)

func main() {
	// Connect and create table.
	db, err := sql.Open("postgres", "")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	_, err = db.Exec(`create temp table users (name text, age int)`)
	if err != nil {
		log.Fatal(err)
	}

	// Need to start transaction and prepare a statement.
	tx, err := db.Begin()
	if err != nil {
		log.Fatal(err)
	}
	stmt, err := tx.Prepare(`copy users (name, age) from stdin`)
	if err != nil {
		log.Fatal(err)
	}

	// Insert rows.
	users := []struct {
		Name string
		Age  int
	}{
		{"Donald Duck", 36},
		{"Scrooge McDuck", 86},
	}
	for _, user := range users {
		_, err = stmt.Exec(user.Name, user.Age)
		if err != nil {
			log.Fatal(err)
		}
	}

	// Finalize copy and statement, and commit transaction.
	if _, err := stmt.Exec(); err != nil {
		log.Fatal(err)
	}
	if err := stmt.Close(); err != nil {
		log.Fatal(err)
	}
	if err := tx.Commit(); err != nil {
		log.Fatal(err)
	}

	// Query rows to verify.
	rows, err := db.Query(`select * from users order by name`)
	if err != nil {
		log.Fatal(err)
	}
	for rows.Next() {
		var (
			name string
			age  int
		)
		err := rows.Scan(&name, &age)
		if err != nil {
			log.Fatal(err)
		}
		fmt.Println(name, age)
	}

}

Output:
Donald Duck 36
Scrooge McDuck 86
Example (Open) 
package main

import (
	"database/sql"
	"log"
)

func main() {
	// Or as URL: postgresql://localhost/pqgo
	db, err := sql.Open("postgres", "dbname=pqgo")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	// db.Open() only creates a connection pool, and doesn't actually establish
	// a connection to the database. To ensure the connection works you need to
	// do *something* with a connection.
	err = db.Ping()
	if err != nil {
		log.Fatal(err)
	}
}
Example (OpenConfig) 
package main

import (
	"database/sql"
	"log"

	"github.com/lib/pq"
)

func main() {
	cfg := pq.Config{
		Host: "localhost",
		Port: 5432,
		User: "pqgo",
	}
	// Or: create a new Config from the defaults, environment, and DSN.
	// cfg, err := pq.NewConfig("host=postgres dbname=pqgo")
	// if err != nil {
	//     log.Fatal(err)
	// }

	c, err := pq.NewConnectorConfig(cfg)
	if err != nil {
		log.Fatal(err)
	}

	// Create connection pool.
	db := sql.OpenDB(c)
	defer db.Close()

	// Make sure it works.
	err = db.Ping()
	if err != nil {
		log.Fatal(err)
	}
}
Example (TimestampWithTimezone) 
package main

import (
	"database/sql"
	"fmt"
	"log"
	"time"
)

func main() {
	dbUTC, err := sql.Open("postgres", "dbname=pqgo timezone=UTC")
	if err != nil {
		log.Fatal(err)
	}
	defer dbUTC.Close()

	dbPL, err := sql.Open("postgres", "dbname=pqgo timezone=Asia/Gaza")
	if err != nil {
		log.Fatal(err)
	}
	defer dbPL.Close()

	var tsUTC, tsPL time.Time
	err = dbUTC.QueryRow(`select '2026-03-15 17:45:47Z'::timestamptz`).Scan(&tsUTC)
	if err != nil {
		log.Fatal(err)
	}
	err = dbPL.QueryRow(`select '2026-03-15 17:45:47Z'::timestamptz`).Scan(&tsPL)
	if err != nil {
		log.Fatal(err)
	}

	fmt.Println("timestamptz in UTC:      ", tsUTC)
	fmt.Println("timestamptz in Asia/Gaza:", tsPL)
	fmt.Println("Equal():                 ", tsUTC.Equal(tsPL))
}

Output:
timestamptz in UTC:       2026-03-15 17:45:47 +0000 UTC
timestamptz in Asia/Gaza: 2026-03-15 19:45:47 +0200 EET
Equal():                  true
Example (TimestampWithoutTimezone) 
package main

import (
	"database/sql"
	"fmt"
	"log"
	"time"
)

func main() {
	db, err := sql.Open("postgres", "dbname=pqgo timezone=UTC")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	var ts time.Time
	err = db.QueryRow(`select '2026-03-15 17:45:47'::timestamp`).Scan(&ts)
	if err != nil {
		log.Fatal(err)
	}

	z, o := ts.Zone()
	fmt.Println("timestamp :               ", ts)
	fmt.Printf("Zone():                    %q %v\n", z, o)
	fmt.Println("Location() == time.UTC:   ", ts.Location() == time.UTC)
	fmt.Println("Location() == FixedZone:  ", ts.Location() == time.FixedZone("", 0))

}

Output:
timestamp :                2026-03-15 17:45:47 +0000 +0000
Zone():                    "" 0
Location() == time.UTC:    false
Location() == FixedZone:   true

Index

Examples

Constants

View Source 
const (
	// No SSL
	SSLModeDisable = SSLMode("disable")

	// First try a non-SSL connection and if that fails try an SSL connection.
	SSLModeAllow = SSLMode("allow")

	// First try an SSL connection and if that fails try a non-SSL connection.
	SSLModePrefer = SSLMode("prefer")

	// Require SSL, but skip verification. This is the default.
	SSLModeRequire = SSLMode("require")

	// Require SSL and verify that the certificate was signed by a trusted CA.
	SSLModeVerifyCA = SSLMode("verify-ca")

	// Require SSL and verify that the certificate was signed by a trusted CA
	// and the server host name matches the one in the certificate.
	SSLModeVerifyFull = SSLMode("verify-full")
)

Values for SSLMode that pq supports.

View Source 
const (
	// Negotiate whether SSL should be used. This is the default.
	SSLNegotiationPostgres = SSLNegotiation("postgres")

	// Always use SSL, don't try to negotiate.
	SSLNegotiationDirect = SSLNegotiation("direct")
)

Values for SSLNegotiation that pq supports.

View Source 
const (
	// Any successful connection is acceptable. This is the default.
	TargetSessionAttrsAny = TargetSessionAttrs("any")

	// Session must accept read-write transactions by default: the server must
	// not be in hot standby mode and default_transaction_read_only must be
	// off.
	TargetSessionAttrsReadWrite = TargetSessionAttrs("read-write")

	// Session must not accept read-write transactions by default.
	TargetSessionAttrsReadOnly = TargetSessionAttrs("read-only")

	// Server must not be in hot standby mode.
	TargetSessionAttrsPrimary = TargetSessionAttrs("primary")

	// Server must be in hot standby mode.
	TargetSessionAttrsStandby = TargetSessionAttrs("standby")

	// First try to find a standby server, but if none of the listed hosts is a
	// standby server, try again in any mode.
	TargetSessionAttrsPreferStandby = TargetSessionAttrs("prefer-standby")
)

Values for TargetSessionAttrs that pq supports.

View Source 
const (
	// Don't load balance; try hosts in the order in which they're provided.
	// This is the default.
	LoadBalanceHostsDisable = LoadBalanceHosts("disable")

	// Hosts are tried in random order to balance connections across multiple
	// PostgreSQL servers.
	//
	// When using this value it's recommended to also configure a reasonable
	// value for connect_timeout. Because then, if one of the nodes that are
	// used for load balancing is not responding, a new node will be tried.
	LoadBalanceHostsRandom = LoadBalanceHosts("random")
)

Values for LoadBalanceHosts that pq supports.

View Source 
const (
	// ProtocolVersion30 is the default protocol version, supported in
	// PostgreSQL 3.0 and newer.
	ProtocolVersion30 = ProtocolVersion("3.0")

	// ProtocolVersion32 uses a longer secret key length for query cancellation,
	// supported in PostgreSQL 18 and newer.
	ProtocolVersion32 = ProtocolVersion("3.2")

	// ProtocolVersionLatest is the latest protocol version that pq supports
	// (which may not be supported by the server).
	ProtocolVersionLatest = ProtocolVersion("latest")
)

Values for ProtocolVersion that pq supports.

View Source 
const (
	SSLProtocolVersionTLS10 = SSLProtocolVersion("TLSv1.0")
	SSLProtocolVersionTLS11 = SSLProtocolVersion("TLSv1.1")
	SSLProtocolVersionTLS12 = SSLProtocolVersion("TLSv1.2")
	SSLProtocolVersionTLS13 = SSLProtocolVersion("TLSv1.3")
)

Values for SSLProtocolVersion that pq supports.

View Source 
const (
	Efatal   = pqerror.SeverityFatal
	Epanic   = pqerror.SeverityPanic
	Ewarning = pqerror.SeverityWarning
	Enotice  = pqerror.SeverityNotice
	Edebug   = pqerror.SeverityDebug
	Einfo    = pqerror.SeverityInfo
	Elog     = pqerror.SeverityLog
)

pq.Error.Severity values.

Deprecated: use pqerror.Severity[..] values.

Variables

View Source 
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")
	ErrCouldNotDetectUsername    = errors.New("pq: could not detect default username; please provide one explicitly")
	ErrSSLKeyUnknownOwnership    = pqutil.ErrSSLKeyUnknownOwnership
	ErrSSLKeyHasWorldPermissions = pqutil.ErrSSLKeyHasWorldPermissions
)

Common error types

View Source 
var ErrChannelAlreadyOpen = errors.New("pq: channel is already open")

ErrChannelAlreadyOpen is returned from Listen when a channel is already open.

View Source 
var ErrChannelNotOpen = errors.New("pq: channel is not open")

ErrChannelNotOpen is returned from Unlisten when a channel is not open.

Functions

func Array 

func Array(a any) interface {
	driver.Valuer
	sql.Scanner
}

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.

func BufferQuoteIdentifier added in v1.10.8 

func BufferQuoteIdentifier(name string, buffer *bytes.Buffer)

BufferQuoteIdentifier satisfies the same purpose as QuoteIdentifier, but backed by a byte buffer.

func ConnectorNoticeHandler added in v1.4.0 

func ConnectorNoticeHandler(c driver.Connector) func(*Error)

ConnectorNoticeHandler returns the currently set notice handler, if any. If the given connector is not a result of ConnectorWithNoticeHandler, nil is returned.

func ConnectorNotificationHandler added in v1.5.1 

func ConnectorNotificationHandler(c driver.Connector) func(*Notification)

ConnectorNotificationHandler returns the currently set notification handler, if any. If the given connector is not a result of ConnectorWithNotificationHandler, nil is returned.

func CopyIn deprecated 

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

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

func CopyInSchema deprecated 

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

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

func DialOpen 

func DialOpen(d Dialer, dsn string) (_ driver.Conn, err error)

DialOpen opens a new connection to the database using a dialer.

func EnableInfinityTs 

func EnableInfinityTs(negative time.Time, positive time.Time)

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.

func FormatTimestamp 

func FormatTimestamp(t time.Time) []byte

FormatTimestamp formats t into Postgres' text format for timestamps.

func NoticeHandler added in v1.4.0 

func NoticeHandler(c driver.Conn) func(*Error)

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.

func Open 

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

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.

func ParseTimestamp 

func ParseTimestamp(currentLocation *time.Location, str string) (time.Time, error)

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.

func ParseURL deprecated 

func ParseURL(url string) (string, error)

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 QuoteIdentifier 

func QuoteIdentifier(name string) string

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.

func QuoteLiteral added in v1.2.0 

func QuoteLiteral(literal string) string

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.

func RegisterGSSProvider added in v1.7.0 

func RegisterGSSProvider(newGssArg NewGSSFunc)

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: 

import "github.com/lib/pq/auth/kerberos"

func init() {
	pq.RegisterGSSProvider(func() (pq.GSS, error) { return kerberos.NewGSS() })
}

func RegisterTLSConfig added in v1.11.0 

func RegisterTLSConfig(key string, config *tls.Config) error

RegisterTLSConfig registers a custom tls.Config. They are used by using sslmode=pqgo-«key» in the connection string.

Set the config to nil to remove a configuration.

Example 
package main

import (
	"crypto/tls"
	"crypto/x509"
	"database/sql"
	"log"
	"os"

	"github.com/lib/pq"
)

func main() {
	pem, err := os.ReadFile("testdata/init/root.crt")
	if err != nil {
		log.Fatal(err)
	}

	root := x509.NewCertPool()
	root.AppendCertsFromPEM(pem)

	certs, err := tls.LoadX509KeyPair("testdata/init/postgresql.crt", "testdata/init/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 added in v1.4.0 

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

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.

func SetNotificationHandler added in v1.5.0 

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

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.

Types

type ArrayDelimiter 

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.

type BoolArray 

type BoolArray []bool

BoolArray represents a one-dimensional array of the PostgreSQL boolean type.

func (*BoolArray) Scan 

func (a *BoolArray) Scan(src any) error

Scan implements the sql.Scanner interface.

func (BoolArray) Value 

func (a BoolArray) Value() (driver.Value, error)

Value implements the driver.Valuer interface.

type ByteaArray 

type ByteaArray [][]byte

ByteaArray represents a one-dimensional array of the PostgreSQL bytea type.

func (*ByteaArray) Scan 

func (a *ByteaArray) Scan(src any) error

Scan implements the sql.Scanner interface.

func (ByteaArray) Value 

func (a ByteaArray) Value() (driver.Value, error)

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

type Config added in v1.11.0 

type Config struct {
	// The host to connect to. Absolute paths and values that start with @ are
	// for unix domain sockets. Defaults to localhost.
	//
	// A comma-separated list of host names is also accepted, in which case each
	// host name in the list is tried in order or randomly if load_balance_hosts
	// is set. An empty item selects the default of localhost. The
	// target_session_attrs option controls properties the host must have to be
	// considered acceptable.
	Host string `postgres:"host" env:"PGHOST"`

	// IPv4 or IPv6 address to connect to. Using hostaddr allows the application
	// to avoid a host name lookup, which might be important in applications
	// with time constraints. A hostname is required for sslmode=verify-full and
	// the GSSAPI or SSPI authentication methods.
	//
	// The following rules are used:
	//
	// - If host is given without hostaddr, a host name lookup occurs.
	//
	// - If hostaddr is given without host, the value for hostaddr gives the
	//   server network address. The connection attempt will fail if the
	//   authentication method requires a host name.
	//
	// - If both host and hostaddr are given, the value for hostaddr gives the
	//   server network address. The value for host is ignored unless the
	//   authentication method requires it, in which case it will be used as the
	//   host name.
	//
	// A comma-separated list of hostaddr values is also accepted, in which case
	// each host in the list is tried in order or randonly if load_balance_hosts
	// is set. An empty item causes the corresponding host name to be used, or
	// the default host name if that is empty as well. The target_session_attrs
	// option controls properties the host must have to be considered
	// acceptable.
	Hostaddr netip.Addr `postgres:"hostaddr" env:"PGHOSTADDR"`

	// The port to connect to. Defaults to 5432.
	//
	// If multiple hosts were given in the host or hostaddr parameters, this
	// parameter may specify a comma-separated list of ports of the same length
	// as the host list, or it may specify a single port number to be used for
	// all hosts. An empty string, or an empty item in a comma-separated list,
	// specifies the default of 5432.
	Port uint16 `postgres:"port" env:"PGPORT"`

	// The name of the database to connect to.
	Database string `postgres:"dbname" env:"PGDATABASE"`

	// The user to sign in as. Defaults to the current user.
	User string `postgres:"user" env:"PGUSER"`

	// The user's password.
	Password string `postgres:"password" env:"PGPASSWORD"`

	// Path to [pgpass] file to store passwords; overrides Password.
	//
	// [pgpass]: http://www.postgresql.org/docs/current/static/libpq-pgpass.html
	Passfile string `postgres:"passfile" env:"PGPASSFILE"`

	// Commandline options to send to the server at connection start.
	Options string `postgres:"options" env:"PGOPTIONS"`

	// Application name, displayed in pg_stat_activity and log entries.
	ApplicationName string `postgres:"application_name" env:"PGAPPNAME"`

	// Used if application_name is not given. Specifying a fallback name is
	// useful in generic utility programs that wish to set a default application
	// name but allow it to be overridden by the user.
	FallbackApplicationName string `postgres:"fallback_application_name" env:"-"`

	// Whether to use SSL. Defaults to "require" (different from libpq's default
	// of "prefer").
	//
	// [RegisterTLSConfig] can be used to registers a custom [tls.Config], which
	// can be used by setting sslmode=pqgo-«key» in the connection string.
	SSLMode SSLMode `postgres:"sslmode" env:"PGSSLMODE"`

	// When set to "direct" it will use SSL without negotiation (PostgreSQL ≥17 only).
	SSLNegotiation SSLNegotiation `postgres:"sslnegotiation" env:"PGSSLNEGOTIATION"`

	// Path to client SSL certificate. The file must contain PEM encoded data.
	//
	// Defaults to ~/.postgresql/postgresql.crt
	SSLCert string `postgres:"sslcert" env:"PGSSLCERT"`

	// Path to secret key for sslcert. The file must contain PEM encoded data.
	//
	// Defaults to ~/.postgresql/postgresql.key
	SSLKey string `postgres:"sslkey" env:"PGSSLKEY"`

	// Path to root certificate. The file must contain PEM encoded data.
	//
	// The special value "system" can be used to load the system's root
	// certificates ([x509.SystemCertPool]). This will change the default
	// sslmode to verify-full and issue an error if a lower setting is used – as
	// anyone can register a valid certificate hostname verification becomes
	// essential.
	//
	// Defaults to ~/.postgresql/root.crt.
	SSLRootCert string `postgres:"sslrootcert" env:"PGSSLROOTCERT"`

	// By default SNI is on, any value which is not starting with "1" disables
	// SNI.
	SSLSNI bool `postgres:"sslsni" env:"PGSSLSNI"`

	// Minimum SSL/TLS protocol version to allow for the connection.
	//
	// The default is determined by [tls.Config.MinVersion], which is TLSv1.2 at
	// the time of writing.
	SSLMinProtocolVersion SSLProtocolVersion `postgres:"ssl_min_protocol_version" env:"SSLPGMINPROTOCOLVERSION"`

	// Maximum SSL/TLS protocol version to allow for the connection. If not set,
	// this parameter is ignored and the connection will use the maximum bound
	// defined by the backend, if set. Setting the maximum protocol version is
	// mainly useful for testing or if some component has issues working with a
	// newer protocol.
	SSLMaxProtocolVersion SSLProtocolVersion `postgres:"ssl_max_protocol_version" env:"SSLPGMAXPROTOCOLVERSION"`

	// Interpert sslcert and sslkey as PEM encoded data, rather than a path to a
	// PEM file. This is a pq extension, not supported in libpq.
	SSLInline bool `postgres:"sslinline" env:"-"`

	// 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.
	KrbSrvname string `postgres:"krbsrvname" env:"PGKRBSRVNAME"`

	// GSS (Kerberos) SPN. This takes priority over krbsrvname if present. This
	// is a pq extension, not supported in libpq.
	KrbSpn string `postgres:"krbspn" env:"-"`

	// Maximum time to wait while connecting, in seconds. Zero, negative, or not
	// specified means wait indefinitely
	ConnectTimeout time.Duration `postgres:"connect_timeout" env:"PGCONNECT_TIMEOUT"`

	// Whether to always send []byte parameters over as binary. Enables single
	// round-trip mode for non-prepared Query calls. This is a pq extension, not
	// supported in libpq.
	BinaryParameters bool `postgres:"binary_parameters" env:"-"`

	// This connection should never use the binary format when receiving query
	// results from prepared statements. Only provided for debugging. This is a
	// pq extension, not supported in libpq.
	DisablePreparedBinaryResult bool `postgres:"disable_prepared_binary_result" env:"-"`

	// Client encoding; pq only supports UTF8 and this must be blank or "UTF8".
	ClientEncoding string `postgres:"client_encoding" env:"PGCLIENTENCODING"`

	// Date/time representation to use; pq only supports "ISO, MDY" and this
	// must be blank or "ISO, MDY".
	Datestyle string `postgres:"datestyle" env:"PGDATESTYLE"`

	// Default time zone.
	TZ string `postgres:"tz" env:"PGTZ"`

	// Default mode for the genetic query optimizer.
	Geqo string `postgres:"geqo" env:"PGGEQO"`

	// Determine whether the session must have certain properties to be
	// acceptable. It's typically used in combination with multiple host names
	// to select the first acceptable alternative among several hosts.
	TargetSessionAttrs TargetSessionAttrs `postgres:"target_session_attrs" env:"PGTARGETSESSIONATTRS"`

	// Controls the order in which the client tries to connect to the available
	// hosts. Once a connection attempt is successful no other hosts will be
	// tried. This parameter is typically used in combination with multiple host
	// names.
	//
	// This parameter can be used in combination with target_session_attrs to,
	// for example, load balance over standby servers only. Once successfully
	// connected, subsequent queries on the returned connection will all be sent
	// to the same server.
	LoadBalanceHosts LoadBalanceHosts `postgres:"load_balance_hosts" env:"PGLOADBALANCEHOSTS"`

	// Minimum acceptable PostgreSQL protocol version. If the server does not
	// support at least this version, the connection will fail. Defaults to
	// "3.0".
	MinProtocolVersion ProtocolVersion `postgres:"min_protocol_version" env:"PGMINPROTOCOLVERSION"`

	// Maximum PostgreSQL protocol version to request from the server. Defaults to "3.0".
	MaxProtocolVersion ProtocolVersion `postgres:"max_protocol_version" env:"PGMAXPROTOCOLVERSION"`

	// Load connection parameters from the service file at ~/.pg_service.conf
	// (which can be configured with PGSERVICEFILE).
	//
	// The service file is a INI-like file to configure connection parameters:
	//
	//   [servicename]
	//   # Comment
	//   dbname=foo
	//
	// Unlike libpq, this does not look at the system-wide service file, as the
	// location of this is a compile-time value that is not easy for pq to
	// retrieve.
	Service string `postgres:"service" env:"PGSERVICE"`

	// Path to connection service file. Defaults to ~/.pg_service.conf.
	ServiceFile string `postgres:"-" env:"PGSERVICEFILE"`

	// Runtime parameters: any unrecognized parameter in the DSN will be added
	// to this and sent to PostgreSQL during startup.
	Runtime map[string]string `postgres:"-" env:"-"`

	// Multi contains additional connection details. The first value is
	// available in [Config.Host], [Config.Hostaddr], and [Config.Port], and
	// additional ones (if any) are available here.
	Multi []ConfigMultihost
	// contains filtered or unexported fields
}

Config holds options pq supports when connecting to PostgreSQL.

The postgres struct tag is used for the value from the DSN (e.g. "dbname=abc"), and the env struct tag is used for the environment variable (e.g. "PGDATABASE=abc")

func NewConfig added in v1.11.0 

func NewConfig(dsn string) (Config, error)

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 (Config) Clone added in v1.11.0 

func (cfg Config) Clone() Config

Clone returns a copy of the Config.

type ConfigMultihost added in v1.11.0 

type ConfigMultihost struct {
	Host     string
	Hostaddr netip.Addr
	Port     uint16
}

ConfigMultihost specifies an additional server to try to connect to.

type Connector added in v1.1.0 

type Connector struct {
	// contains filtered or unexported fields
}

Connector represents a fixed configuration for the pq driver with a given dsn. Connector satisfies the database/sql/driver.Connector interface and can be used to create any number of DB Conn's via sql.OpenDB.

func NewConnector 

func NewConnector(dsn string) (*Connector, error)

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.

Example 
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 added in v1.11.0 

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.

func (*Connector) Connect added in v1.1.0 

func (c *Connector) Connect(ctx context.Context) (driver.Conn, error)

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

func (*Connector) Dialer added in v1.10.6 

func (c *Connector) Dialer(dialer Dialer)

Dialer allows change the dialer used to open connections.

func (*Connector) Driver added in v1.1.0 

func (c *Connector) Driver() driver.Driver

Driver returns the underlying driver of this Connector.

type Dialer 

type Dialer interface {
	Dial(network, address string) (net.Conn, error)
	DialTimeout(network, address string, timeout time.Duration) (net.Conn, error)
}

Dialer is the dialer interface. It can be used to obtain more control over how pq creates network connections.

type DialerContext added in v1.1.0 

type DialerContext interface {
	DialContext(ctx context.Context, network, address string) (net.Conn, error)
}

DialerContext is the context-aware dialer interface.

type Driver 

type Driver struct{}

Driver is the Postgres database driver.

func (Driver) Open 

func (d Driver) Open(name string) (driver.Conn, error)

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.

type Error 

type Error struct {
	// [Efatal], [Epanic], [Ewarning], [Enotice], [Edebug], [Einfo], or [Elog].
	// Always present.
	Severity string

	// SQLSTATE code. Always present.
	Code pqerror.Code

	// Primary human-readable error message. This should be accurate but terse
	// (typically one line). Always present.
	Message string

	// Optional secondary error message carrying more detail about the problem.
	// Might run to multiple lines.
	Detail string

	// Optional suggestion what to do about the problem. This is intended to
	// differ from Detail in that it offers advice (potentially inappropriate)
	// rather than hard facts. Might run to multiple lines.
	Hint string

	// error position as an index into the original query string, as decimal
	// ASCII integer. The first character has index 1, and positions are
	// measured in characters not bytes.
	Position string

	// This is defined the same as the Position field, but it is used when the
	// cursor position refers to an internally generated command rather than the
	// one submitted by the client. The InternalQuery field will always appear
	// when this field appears.
	InternalPosition string

	// Text of a failed internally-generated command. This could be, for
	// example, an SQL query issued by a PL/pgSQL function.
	InternalQuery string

	// An indication of the context in which the error occurred. Presently this
	// includes a call stack traceback of active procedural language functions
	// and internally-generated queries. The trace is one entry per line, most
	// recent first.
	Where string

	// If the error was associated with a specific database object, the name of
	// the schema containing that object, if any.
	Schema string

	// If the error was associated with a specific table, the name of the table.
	// (Refer to the schema name field for the name of the table's schema.)
	Table string

	// If the error was associated with a specific table column, the name of the
	// column. (Refer to the schema and table name fields to identify the
	// table.)
	Column string

	// If the error was associated with a specific data type, the name of the
	// data type. (Refer to the schema name field for the name of the data
	// type's schema.)
	DataTypeName string

	// If the error was associated with a specific constraint, the name of the
	// constraint. Refer to fields listed above for the associated table or
	// domain. (For this purpose, indexes are treated as constraints, even if
	// they weren't created with constraint syntax.)
	Constraint string

	// File name of the source-code location where the error was reported.
	File string

	// Line number of the source-code location where the error was reported.
	Line string

	// Name of the source-code routine reporting the error.
	Routine string
	// contains filtered or unexported fields
}

Error returned by the PostgreSQL server.

The Error method returns the error message and error code: 

pq: invalid input syntax for type json (22P02)

The [ErrorWithDetail] method also includes the error Detail, Hint, and location context (if any): 

ERROR:   invalid input syntax for type json (22P02)
DETAIL:  Token "asd" is invalid.
CONTEXT: line 5, column 8:

 3 | 'def',
 4 | 123,
 5 | 'foo', 'asd'::jsonb
            ^

func As added in v1.12.0 

func As(err error, codes ...ErrorCode) *Error

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.

Example 
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 (*Error) Error 

func (e *Error) Error() string

func (*Error) ErrorWithDetail added in v1.11.0 

func (e *Error) ErrorWithDetail() string

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

See the documentation on Error.

func (*Error) Fatal 

func (e *Error) Fatal() bool

Fatal returns true if the Error Severity is fatal.

func (*Error) Get deprecated 

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

Get implements the legacy PGError interface.

Deprecated: new code should use the fields of the Error struct directly.

func (*Error) SQLState added in v1.10.6 

func (e *Error) SQLState() string

SQLState returns the SQLState of the error.

type ErrorClass deprecated 

type ErrorClass = pqerror.Class

ErrorClass is only the class part of an error code.

Deprecated: use pqerror.Class

type ErrorCode deprecated 

type ErrorCode = pqerror.Code

ErrorCode is a five-character error code.

Deprecated: use pqerror.Code

type EventCallbackType 

type EventCallbackType func(event ListenerEventType, err error)

EventCallbackType is the event callback type. See also ListenerEventType constants' documentation.

type Float32Array added in v1.9.0 

type Float32Array []float32

Float32Array represents a one-dimensional array of the PostgreSQL double precision type.

func (*Float32Array) Scan added in v1.9.0 

func (a *Float32Array) Scan(src any) error

Scan implements the sql.Scanner interface.

func (Float32Array) Value added in v1.9.0 

func (a Float32Array) Value() (driver.Value, error)

Value implements the driver.Valuer interface.

type Float64Array 

type Float64Array []float64

Float64Array represents a one-dimensional array of the PostgreSQL double precision type.

func (*Float64Array) Scan 

func (a *Float64Array) Scan(src any) error

Scan implements the sql.Scanner interface.

func (Float64Array) Value 

func (a Float64Array) Value() (driver.Value, error)

Value implements the driver.Valuer interface.

type GSS added in v1.7.0 

type GSS interface {
	GetInitToken(host string, service string) ([]byte, error)
	GetInitTokenFromSpn(spn string) ([]byte, error)
	Continue(inToken []byte) (done bool, outToken []byte, err error)
}

GSS provides GSSAPI authentication (e.g., Kerberos).

type GenericArray 

type GenericArray struct{ A any }

GenericArray implements the driver.Valuer and sql.Scanner interfaces for an array or slice of any dimension.

func (GenericArray) Scan 

func (a GenericArray) Scan(src any) error

Scan implements the sql.Scanner interface.

func (GenericArray) Value 

func (a GenericArray) Value() (driver.Value, error)

Value implements the driver.Valuer interface.

type Int32Array added in v1.9.0 

type Int32Array []int32

Int32Array represents a one-dimensional array of the PostgreSQL integer types.

func (*Int32Array) Scan added in v1.9.0 

func (a *Int32Array) Scan(src any) error

Scan implements the sql.Scanner interface.

func (Int32Array) Value added in v1.9.0 

func (a Int32Array) Value() (driver.Value, error)

Value implements the driver.Valuer interface.

type Int64Array 

type Int64Array []int64

Int64Array represents a one-dimensional array of the PostgreSQL integer types.

func (*Int64Array) Scan 

func (a *Int64Array) Scan(src any) error

Scan implements the sql.Scanner interface.

func (Int64Array) Value 

func (a Int64Array) Value() (driver.Value, error)

Value implements the driver.Valuer interface.

type Listener 

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

Listener can safely be used from concurrently running goroutines.

Example 
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

func NewDialListener 

func NewDialListener(d Dialer, dsn string, minReconnect, maxReconnect time.Duration, cb EventCallbackType) *Listener

NewDialListener is like NewListener but it takes a Dialer.

func NewListener 

func NewListener(dsn string, minReconnect, maxReconnect time.Duration, cb EventCallbackType) *Listener

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.

func (*Listener) Close 

func (l *Listener) Close() error

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.

func (*Listener) Listen 

func (l *Listener) Listen(channel string) error

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 (*Listener) NotificationChannel 

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.

func (*Listener) Ping 

func (l *Listener) Ping() error

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

func (*Listener) Unlisten 

func (l *Listener) Unlisten(channel string) error

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 (*Listener) UnlistenAll 

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 

type ListenerConn struct {
	// contains filtered or unexported fields
}

ListenerConn is a low-level interface for waiting for notifications. You should use Listener instead.

func NewListenerConn 

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

NewListenerConn creates a new ListenerConn. Use NewListener instead.

func (*ListenerConn) Close 

func (l *ListenerConn) Close() error

Close closes the connection.

func (*ListenerConn) Err 

func (l *ListenerConn) Err() error

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

func (*ListenerConn) ExecSimpleQuery 

func (l *ListenerConn) ExecSimpleQuery(q string) (executed bool, err error)

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.

func (*ListenerConn) Listen 

func (l *ListenerConn) Listen(channel string) (bool, error)

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

func (*ListenerConn) Ping 

func (l *ListenerConn) Ping() error

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

func (*ListenerConn) Unlisten 

func (l *ListenerConn) Unlisten(channel string) (bool, error)

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

func (*ListenerConn) UnlistenAll 

func (l *ListenerConn) UnlistenAll() (bool, error)

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

type ListenerEventType 

type ListenerEventType int

ListenerEventType is an enumeration of listener event types. 

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
)

func (ListenerEventType) String added in v1.12.0 

func (l ListenerEventType) String() string

type LoadBalanceHosts added in v1.11.0 

type LoadBalanceHosts string

LoadBalanceHosts is a load_balance_hosts setting.

type NewGSSFunc added in v1.7.0 

type NewGSSFunc func() (GSS, error)

NewGSSFunc creates a GSS authentication provider, for use with RegisterGSSProvider.

type NoticeHandlerConnector added in v1.4.0 

type NoticeHandlerConnector struct {
	driver.Connector
	// contains filtered or unexported fields
}

NoticeHandlerConnector wraps a regular connector and sets a notice handler on it.

func ConnectorWithNoticeHandler added in v1.4.0 

func ConnectorWithNoticeHandler(c driver.Connector, handler func(*Error)) *NoticeHandlerConnector

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() {
	// 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 

func (n *NoticeHandlerConnector) Connect(ctx context.Context) (driver.Conn, error)

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

type Notification 

type Notification struct {
	BePid   int    // Process ID (PID) of the notifying postgres backend.
	Channel string // Name of the channel the notification was sent on.
	Extra   string // Payload, or the empty string if unspecified.
}

Notification represents a single notification from the database.

type NotificationHandlerConnector added in v1.5.1 

type NotificationHandlerConnector struct {
	driver.Connector
	// contains filtered or unexported fields
}

NotificationHandlerConnector wraps a regular connector and sets a notification handler on it.

func ConnectorWithNotificationHandler added in v1.5.1 

func ConnectorWithNotificationHandler(c driver.Connector, handler func(*Notification)) *NotificationHandlerConnector

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 

func (n *NotificationHandlerConnector) Connect(ctx context.Context) (driver.Conn, error)

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

type NullTime deprecated 

type NullTime = sql.NullTime

NullTime represents a time.Time that may be null.

Deprecated: this is an alias for sql.NullTime.

type PGError deprecated 

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

PGError is an interface used by previous versions of pq.

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

type ProtocolVersion added in v1.12.0 

type ProtocolVersion string

ProtocolVersion is a min_protocol_version or max_protocol_version setting.

type SSLMode added in v1.11.0 

type SSLMode string

SSLMode is a sslmode setting.

type SSLNegotiation added in v1.11.0 

type SSLNegotiation string

SSLNegotiation is a sslnegotiation setting.

type SSLProtocolVersion added in v1.12.0 

type SSLProtocolVersion string

SSLProtocolVersion is a ssl_min_protocol_version or ssl_max_protocol_version setting.

type StringArray 

type StringArray []string

StringArray represents a one-dimensional array of the PostgreSQL character types.

func (*StringArray) Scan 

func (a *StringArray) Scan(src any) error

Scan implements the sql.Scanner interface.

func (StringArray) Value 

func (a StringArray) Value() (driver.Value, error)

Value implements the driver.Valuer interface.

type TargetSessionAttrs added in v1.11.0 

type TargetSessionAttrs string

TargetSessionAttrs is a target_session_attrs setting.

Source Files

View all Source files

Directories

Path Synopsis
auth
kerberos module
internal
pgpass
pgservice
pqsql
pqtest
pqtime
pqutil
proto
Package oid contains OID constants as defined by the Postgres server.
 Package oid contains OID constants as defined by the Postgres server.
Package pqerror contains PostgreSQL error codes for use with pq.Error.
 Package pqerror contains PostgreSQL error codes for use with pq.Error.
Package scram implements a SCRAM-{SHA-1,etc} client per RFC5802.
 Package scram implements a SCRAM-{SHA-1,etc} client per RFC5802.

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.