Overview ▹

Overview ▾

Package pgtype converts between Go and PostgreSQL values.

The primary type is the Map type. It is a map of PostgreSQL types identified by OID (object ID) to a Codec. A Codec is responsible for converting between Go and PostgreSQL values. NewMap creates a Map with all supported standard PostgreSQL types already registered. Additional types can be registered with Map.RegisterType.

Use Map.Scan and Map.Encode to decode PostgreSQL values to Go and encode Go values to PostgreSQL respectively.

Base Type Mapping

pgtype maps between all common base types directly between Go and PostgreSQL. In particular:

Go           PostgreSQL
-----------------------
string        varchar
              text

// Integers are automatically be converted to any other integer type if
// it can be done without overflow or underflow.
int8
int16         smallint
int32         int
int64         bigint
int
uint8
uint16
uint32
uint64
uint

// Floats are strict and do not automatically convert like integers.
float32       float4
float64       float8

time.Time     date
              timestamp
              timestamptz

netip.Addr    inet
netip.Prefix  cidr

[]byte        bytea

Null Values

pgtype can map NULLs in two ways. The first is types that can directly represent NULL such as Int4. They work in a similar fashion to database/sql. The second is to use a pointer to a pointer.

var foo pgtype.Text
var bar *string
err := conn.QueryRow("select foo, bar from widgets where id=$1", 42).Scan(&foo, &bar)
if err != nil {
    return err
}

JSON Support

pgtype automatically marshals and unmarshals data from json and jsonb PostgreSQL types.

Extending Existing PostgreSQL Type Support

Generally, all Codecs will support interfaces that can be implemented to enable scanning and encoding. For example, PointCodec can use any Go type that implements the PointScanner and PointValuer interfaces. So rather than use pgtype.Point and application can directly use its own point type with pgtype as long as it implements those interfaces.

See example_custom_type_test.go for an example of a custom type for the PostgreSQL point type.

Sometimes pgx supports a PostgreSQL type such as numeric but the Go type is in an external package that does not have pgx support such as github.com/shopspring/decimal. These types can be registered with pgtype with custom conversion logic. See https://github.com/jackc/pgx-shopspring-decimal and https://github.com/jackc/pgx-gofrs-uuid for example integrations.

New PostgreSQL Type Support

pgtype uses the PostgreSQL OID to determine how to encode or decode a value. pgtype supports array, composite, domain, and enum types. However, any type created in PostgreSQL with CREATE TYPE will receive a new OID. This means that the OID of each new PostgreSQL type must be registered for pgtype to handle values of that type with the correct Codec.

The pgx.Conn LoadType method can return a *Type for array, composite, domain, and enum types by inspecting the database metadata. This *Type can then be registered with Map.RegisterType.

For example, the following function could be called after a connection is established:

func RegisterDataTypes(ctx context.Context, conn *pgx.Conn) error {
  dataTypeNames := []string{
    "foo",
    "_foo",
    "bar",
    "_bar",
  }

  for _, typeName := range dataTypeNames {
    dataType, err := conn.LoadType(ctx, typeName)
    if err != nil {
      return err
    }
    conn.TypeMap().RegisterType(dataType)
  }

  return nil
}

A type cannot be registered unless all types it depends on are already registered. e.g. An array type cannot be registered until its element type is registered.

ArrayCodec implements support for arrays. If pgtype supports type T then it can easily support []T by registering an ArrayCodec for the appropriate PostgreSQL OID. In addition, Array[T] type can support multi-dimensional arrays.

CompositeCodec implements support for PostgreSQL composite types. Go structs can be scanned into if the public fields of the struct are in the exact order and type of the PostgreSQL type or by implementing CompositeIndexScanner and CompositeIndexGetter.

Domain types are treated as their underlying type if the underlying type and the domain type are registered.

PostgreSQL enums can usually be treated as text. However, EnumCodec implements support for interning strings which can reduce memory usage.

While pgtype will often still work with unregistered types it is highly recommended that all types be registered due to an improvement in performance and the elimination of certain edge cases.

If an entirely new PostgreSQL type (e.g. PostGIS types) is used then the application or a library can create a new Codec. Then the OID / Codec mapping can be registered with Map.RegisterType. There is no difference between a Codec defined and registered by the application and a Codec built in to pgtype. See any of the Codecs in pgtype for Codec examples and for examples of type registration.

Encoding Unknown Types

pgtype works best when the OID of the PostgreSQL type is known. But in some cases such as using the simple protocol the OID is unknown. In this case Map.RegisterDefaultPgType can be used to register an assumed OID for a particular Go type.

Renamed Types

If pgtype does not recognize a type and that type is a renamed simple type simple (e.g. type MyInt32 int32) pgtype acts as if it is the underlying type. It currently cannot automatically detect the underlying type of renamed structs (eg.g. type MyTime time.Time).

Compatibility with database/sql

pgtype also includes support for custom types implementing the database/sql.Scanner and database/sql/driver.Valuer interfaces.

Child Records

pgtype's support for arrays and composite records can be used to load records and their children in a single query. See example_child_records_test.go for an example.

Overview of Scanning Implementation

The first step is to use the OID to lookup the correct Codec. If the OID is unavailable, Map will try to find the OID from previous calls of Map.RegisterDefaultPgType. The Map will call the Codec's PlanScan method to get a plan for scanning into the Go value. A Codec will support scanning into one or more Go types. Oftentime these Go types are interfaces rather than explicit types. For example, PointCodec can use any Go type that implements the PointScanner and PointValuer interfaces.

If a Go value is not supported directly by a Codec then Map will try wrapping it with additional logic and try again. For example, Int8Codec does not support scanning into a renamed type (e.g. type myInt64 int64). But Map will detect that myInt64 is a renamed type and create a plan that converts the value to the underlying int64 type and then passes that to the Codec (see TryFindUnderlyingTypeScanPlan).

These plan wrappers are contained in Map.TryWrapScanPlanFuncs. By default these contain shared logic to handle renamed types, pointers to pointers, slices, composite types, etc. Additional plan wrappers can be added to seamlessly integrate types that do not support pgx directly. For example, the before mentioned https://github.com/jackc/pgx-shopspring-decimal package detects decimal.Decimal values, wraps them in something implementing NumericScanner and passes that to the Codec.

Map.Scan and Map.Encode are convenience methods that wrap Map.PlanScan and Map.PlanEncode. Determining how to scan or encode a particular type may be a time consuming operation. Hence the planning and execution steps of a conversion are internally separated.

Reducing Compiled Binary Size

pgx.QueryExecModeExec and pgx.QueryExecModeSimpleProtocol require the default PostgreSQL type to be registered for each Go type used as a query parameter. By default pgx does this for all supported types and their array variants. If an application does not use those query execution modes or manually registers the default PostgreSQL type for the types it uses as query parameters it can use the build tag nopgxregisterdefaulttypes. This omits the default type registration and reduces the compiled binary size by ~2MB.

Do not edit. Generated from pgtype/int.go.erb

▹ Example (ChildRecords)

▾ Example (ChildRecords)

This example uses a single query to return parent and child records.

Code:

package pgtype_test

import (
    "context"
    "fmt"
    "os"
    "time"

    "github.com/jackc/pgx/v5"
)

type Player struct {
    Name     string
    Position string
}

type Team struct {
    Name    string
    Players []Player
}

// This example uses a single query to return parent and child records.
func Example_childRecords() {
    ctx, cancel := context.WithTimeout(context.Background(), 120*time.Second)
    defer cancel()

    conn, err := pgx.Connect(ctx, os.Getenv("PGX_TEST_DATABASE"))
    if err != nil {
        fmt.Printf("Unable to establish connection: %v", err)
        return
    }

    if conn.PgConn().ParameterStatus("crdb_version") != "" {
        // Skip test / example when running on CockroachDB. Since an example can't be skipped fake success instead.
        fmt.Println(`Alpha
  Adam: wing
  Bill: halfback
  Charlie: fullback
Beta
  Don: halfback
  Edgar: halfback
  Frank: fullback`)
        return
    }

    // Setup example schema and data.
    _, err = conn.Exec(ctx, `
create temporary table teams (
    name text primary key
);

create temporary table players (
    name text primary key,
    team_name text,
    position text
);

insert into teams (name) values
    ('Alpha'),
    ('Beta');

insert into players (name, team_name, position) values
    ('Adam', 'Alpha', 'wing'),
    ('Bill', 'Alpha', 'halfback'),
    ('Charlie', 'Alpha', 'fullback'),
    ('Don', 'Beta', 'halfback'),
    ('Edgar', 'Beta', 'halfback'),
    ('Frank', 'Beta', 'fullback')
`)
    if err != nil {
        fmt.Printf("Unable to setup example schema and data: %v", err)
        return
    }

    rows, _ := conn.Query(ctx, `
select t.name,
    (select array_agg(row(p.name, position) order by p.name) from players p where p.team_name = t.name)
from teams t
order by t.name
`)
    teams, err := pgx.CollectRows(rows, pgx.RowToStructByPos[Team])
    if err != nil {
        fmt.Printf("CollectRows error: %v", err)
        return
    }

    for _, team := range teams {
        fmt.Println(team.Name)
        for _, player := range team.Players {
            fmt.Printf("  %s: %s\n", player.Name, player.Position)
        }
    }

    // Output:
    // Alpha
    //   Adam: wing
    //   Bill: halfback
    //   Charlie: fullback
    // Beta
    //   Don: halfback
    //   Edgar: halfback
    //   Frank: fullback
}

▹ Example (CustomType)

▾ Example (CustomType)

Code:

package pgtype_test

import (
    "context"
    "fmt"
    "os"

    "github.com/jackc/pgx/v5"
    "github.com/jackc/pgx/v5/pgtype"
)

// Point represents a point that may be null.
type Point struct {
    X, Y  float32 // Coordinates of point
    Valid bool
}

func (p *Point) ScanPoint(v pgtype.Point) error {
    *p = Point{
        X:     float32(v.P.X),
        Y:     float32(v.P.Y),
        Valid: v.Valid,
    }
    return nil
}

func (p Point) PointValue() (pgtype.Point, error) {
    return pgtype.Point{
        P:     pgtype.Vec2{X: float64(p.X), Y: float64(p.Y)},
        Valid: true,
    }, nil
}

func (src *Point) String() string {
    if !src.Valid {
        return "null point"
    }

    return fmt.Sprintf("%.1f, %.1f", src.X, src.Y)
}

func Example_customType() {
    conn, err := pgx.Connect(context.Background(), os.Getenv("PGX_TEST_DATABASE"))
    if err != nil {
        fmt.Printf("Unable to establish connection: %v", err)
        return
    }
    defer conn.Close(context.Background())

    if conn.PgConn().ParameterStatus("crdb_version") != "" {
        // Skip test / example when running on CockroachDB which doesn't support the point type. Since an example can't be
        // skipped fake success instead.
        fmt.Println("null point")
        fmt.Println("1.5, 2.5")
        return
    }

    p := &Point{}
    err = conn.QueryRow(context.Background(), "select null::point").Scan(p)
    if err != nil {
        fmt.Println(err)
        return
    }
    fmt.Println(p)

    err = conn.QueryRow(context.Background(), "select point(1.5,2.5)").Scan(p)
    if err != nil {
        fmt.Println(err)
        return
    }
    fmt.Println(p)
    // Output:
    // null point
    // 1.5, 2.5
}

▹ Example (Json)

▾ Example (Json)

Code:

conn, err := pgx.Connect(context.Background(), os.Getenv("PGX_TEST_DATABASE"))
if err != nil {
    fmt.Printf("Unable to establish connection: %v", err)
    return
}

type person struct {
    Name string `json:"name"`
    Age  int    `json:"age"`
}

input := person{
    Name: "John",
    Age:  42,
}

var output person

err = conn.QueryRow(context.Background(), "select $1::json", input).Scan(&output)
if err != nil {
    fmt.Println(err)
    return
}

fmt.Println(output.Name, output.Age)

Output:

John 42

Name	Synopsis
..
zeronull	Package zeronull contains types that automatically convert between database NULLs and Go zero values.

Package pgtype

Overview ▹

Overview ▾

Base Type Mapping

Null Values

JSON Support

Extending Existing PostgreSQL Type Support

New PostgreSQL Type Support

Encoding Unknown Types

Renamed Types

Child Records

Overview of Scanning Implementation

Reducing Compiled Binary Size

Index ▹

Index ▾

Examples

Package files

Constants

Variables

func GetAssignToDstType ¶

func NullAssignTo ¶

type Array ¶

func (Array[T]) Dimensions ¶

func (Array[T]) Index ¶

func (Array[T]) IndexType ¶

func (Array[T]) ScanIndex ¶

func (Array[T]) ScanIndexType ¶

func (*Array[T]) SetDimensions ¶

type ArrayCodec ¶

func (*ArrayCodec) DecodeDatabaseSQLValue ¶

func (*ArrayCodec) DecodeValue ¶

func (*ArrayCodec) FormatSupported ¶

func (*ArrayCodec) PlanEncode ¶

func (*ArrayCodec) PlanScan ¶

func (*ArrayCodec) PreferredFormat ¶

type ArrayDimension ¶

type ArrayGetter ¶

type ArraySetter ¶

type Bits ¶

func (Bits) BitsValue ¶

func (*Bits) Scan ¶

func (*Bits) ScanBits ¶

func (Bits) Value ¶

type BitsCodec ¶

func (BitsCodec) DecodeDatabaseSQLValue ¶

func (BitsCodec) DecodeValue ¶

func (BitsCodec) FormatSupported ¶

func (BitsCodec) PlanEncode ¶

func (BitsCodec) PlanScan ¶

func (BitsCodec) PreferredFormat ¶

type BitsScanner ¶

type BitsValuer ¶

type Bool ¶

func (Bool) BoolValue ¶

func (Bool) MarshalJSON ¶

func (*Bool) Scan ¶

func (*Bool) ScanBool ¶

func (*Bool) UnmarshalJSON ¶

func (Bool) Value ¶

type BoolCodec ¶

func (BoolCodec) DecodeDatabaseSQLValue ¶

func (BoolCodec) DecodeValue ¶

func (BoolCodec) FormatSupported ¶

func (BoolCodec) PlanEncode ¶

func (BoolCodec) PlanScan ¶

func (BoolCodec) PreferredFormat ¶

type BoolScanner ¶

type BoolValuer ¶

type BoundType ¶

func (BoundType) String ¶

type Box ¶

func (Box) BoxValue ¶

func (*Box) Scan ¶

func (*Box) ScanBox ¶

func (Box) Value ¶

type BoxCodec ¶

func (BoxCodec) DecodeDatabaseSQLValue ¶

func (BoxCodec) DecodeValue ¶

func (BoxCodec) FormatSupported ¶

func (BoxCodec) PlanEncode ¶