pq Documentation

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 Copy Chapter

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 connect_timeout=5")
    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",
	ConnectTimeout: 5 * time.Second,
}
// 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'")

It's recommended to add connect_timeout to the DSN – database/sql may open new
connections asynchronously so it doesn't use the context from QueryContext(),
PingContext(), etc. The default is to wait indefinitely.

Errors Copy Chapter

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))=([email protected]) 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 Copy Chapter

Authentication Copy Section

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` Copy Section

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 Copy Section

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` Copy Section

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 Copy Chapter

LastInsertId Copy Section

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 Copy Section

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 Copy Section

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 Copy Chapter

Running tests Copy Section

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/postgres/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 Copy Section

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