pgx Custom Types Codecs and Composite Values Explained

PostgreSQL lets you define your own types, and teams use them more than they admit: enums for statuses, composite types for addresses, domains for validated text, ranges for time slots. The database handles them natively and a driver has to be told how to speak them, because a type you defined has an OID that exists only in your database. pgx has a clean mechanism for this, a registry of types per connection and codecs that encode and decode them, and it is the part of pgx that most often surprises people. This article works through each kind of type against PostgreSQL 18.6 on Go 1.25.6 with pgx v5.11.0, shows what fails without registration, and how to register.

It belongs to the database cornerstone. It builds on the pgx tour, and the related JSON, array and UUID types are in JSONB, arrays and UUIDs.

How pgx maps types

Every value that crosses the wire has a type OID. pgx keeps a pgtype.Map per connection that maps OIDs and names to codecs, objects that know how to encode and decode a type in the text and binary formats, and to Go types they can convert to and from. The built in types, integers, text, timestamps, uuid, jsonb, numeric and the standard ranges and arrays, are registered by default. Types you create get an OID when you create them, which differs between databases, so pgx cannot know them in advance. For some it can fall back to text; for others it needs you to say what the type is.

The tool is conn.LoadType(ctx, name), which asks the server about a type (is it an enum, a composite, a range, an array of something) and builds the right codec, followed by conn.TypeMap().RegisterType(t). The registry is per connection, so it must run on every connection. The place for that is the pool's AfterConnect hook, which runs once for each new connection.

package main

import (
	"context"
	"fmt"
	"log"

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

const dsn = "postgres://postgres:[email protected]:55432/lab?sslmode=disable"

func must(err error) {
	if err != nil {
		log.Fatal(err)
	}
}

// Address is a Go view of a composite type. Implementing these interfaces tells pgx how to read and write it.
type Address struct {
	Street string
	City   string
	Zip    pgtype.Text
}

func (a *Address) ScanIndex(i int) any {
	switch i {
	case 0:
		return &a.Street
	case 1:
		return &a.City
	case 2:
		return &a.Zip
	}
	return nil
}
func (a *Address) ScanNull() error { *a = Address{}; return nil }
func (a Address) IsNull() bool     { return false }
func (a Address) Index(i int) any {
	switch i {
	case 0:
		return a.Street
	case 1:
		return a.City
	case 2:
		return a.Zip
	}
	return nil
}

type Mood string

func main() {
	ctx := context.Background()
	cfg, err := pgxpool.ParseConfig(dsn)
	must(err)
	// AfterConnect runs on every new connection: this is where types are registered.
	cfg.AfterConnect = func(ctx context.Context, conn *pgx.Conn) error {
		for _, name := range []string{"mood", "_mood", "address", "_address"} {
			t, err := conn.LoadType(ctx, name)
			if err != nil {
				return fmt.Errorf("load %s: %w", name, err)
			}
			conn.TypeMap().RegisterType(t)
		}
		return nil
	}
	admin, err := pgxpool.New(ctx, dsn)
	must(err)
	for _, q := range []string{
		"drop table if exists people672", "drop type if exists address cascade", "drop type if exists mood cascade", "drop domain if exists email672 cascade",
		"create type mood as enum ('sad', 'ok', 'happy')",
		"create type address as (street text, city text, zip text)",
		"create domain email672 as text check (value like '%@%')",
		"create table people672 (id int primary key, mood mood not null, moods mood[] not null default '{}', home address, email email672, hours int4range)",
	} {
		_, err := admin.Exec(ctx, q)
		must(err)
	}
	admin.Close()
	pool, err := pgxpool.NewWithConfig(ctx, cfg)
	must(err)
	defer pool.Close()

	// 1. An enum works as text without any registration, for a single value.
	plain, err := pgxpool.New(ctx, dsn)
	must(err)
	defer plain.Close()
	_, err = plain.Exec(ctx, "insert into people672 (id, mood) values (1, $1)", "happy")
	var m Mood
	must(plain.QueryRow(ctx, "select mood from people672 where id = 1").Scan(&m))
	fmt.Printf("1. enum as text, no registration: stored and read back %q\n", m)

	// 2. An array of enums needs the array type registered, or the encoder does not know it.
	_, err = plain.Exec(ctx, "insert into people672 (id, mood, moods) values (2, 'ok', $1)", []Mood{"sad", "ok"})
	fmt.Println("2. []Mood without registering _mood:", err)
	_, err = pool.Exec(ctx, "insert into people672 (id, mood, moods) values (2, 'ok', $1)", []Mood{"sad", "ok"})
	var moods []Mood
	must(pool.QueryRow(ctx, "select moods from people672 where id = 2").Scan(&moods))
	fmt.Printf("   with LoadType and RegisterType in AfterConnect: err %v, read back %v\n", err, moods)

	// 3. A composite type into a Go struct, and back.
	home := Address{"1 Main St", "Springfield", pgtype.Text{String: "12345", Valid: true}}
	_, err = pool.Exec(ctx, "insert into people672 (id, mood, home) values (3, 'ok', $1)", home)
	must(err)
	var got Address
	must(pool.QueryRow(ctx, "select home from people672 where id = 3").Scan(&got))
	fmt.Printf("3. composite address round trip: %+v\n", got)
	var nul *Address
	must(pool.QueryRow(ctx, "select home from people672 where id = 1").Scan(&nul))
	fmt.Println("   a NULL composite into *Address:", nul == nil)

	// 4. A domain is just its base type to the client, and the server enforces its check.
	_, err = pool.Exec(ctx, "insert into people672 (id, mood, email) values (4, 'ok', 'not an address')")
	fmt.Println("4. domain check:", err)

	// 5. A range type.
	r := pgtype.Range[pgtype.Int4]{Lower: pgtype.Int4{Int32: 9, Valid: true}, Upper: pgtype.Int4{Int32: 17, Valid: true}, LowerType: pgtype.Inclusive, UpperType: pgtype.Exclusive, Valid: true}
	_, err = pool.Exec(ctx, "insert into people672 (id, mood, hours) values (5, 'ok', $1)", r)
	must(err)
	var back pgtype.Range[pgtype.Int4]
	must(pool.QueryRow(ctx, "select hours from people672 where id = 5").Scan(&back))
	var contains bool
	must(pool.QueryRow(ctx, "select hours @> 12 from people672 where id = 5").Scan(&contains))
	fmt.Printf("5. int4range: lower %d inclusive, upper %d exclusive, contains 12: %v\n", back.Lower.Int32, back.Upper.Int32, contains)

	// 6. What the registry knows.
	conn, err := pool.Acquire(ctx)
	must(err)
	defer conn.Release()
	_, ok := conn.Conn().TypeMap().TypeForName("address")
	_, missing := conn.Conn().TypeMap().TypeForName("nothing")
	fmt.Printf("6. the connection's type map knows address: %v, nothing: %v\n", ok, missing)
}

1. enum as text, no registration: stored and read back "happy"
2. []Mood without registering _mood: failed to encode args[0]: unable to encode []main.Mood{"sad", "ok"} into text format for unknown type (OID 49659): cannot find encode plan
   with LoadType and RegisterType in AfterConnect: err <nil>, read back [sad ok]
3. composite address round trip: {Street:1 Main St City:Springfield Zip:{String:12345 Valid:true}}
   a NULL composite into *Address: true
4. domain check: ERROR: value for domain email672 violates check constraint "email672_check" (SQLSTATE 23514)
5. int4range: lower 9 inclusive, upper 17 exclusive, contains 12: true
6. the connection's type map knows address: true, nothing: false

The program creates an enum mood, a composite address, a domain email672 and a table with a column of each, an array of the enum and an int4range. It then uses two pools: a plain one with nothing registered, and one whose configuration registers the types in AfterConnect.

Enums

The first block inserts a mood enum value through the plain pool. It worked, and reading it back into a Mood (a Go string type) worked: stored and read back "happy". An enum is sent and received as text, and pgx can encode a Go string as text for a parameter whose type the server infers from the statement. A single enum value needs no registration.

The second block shows where that stops. Inserting a []Mood through the plain pool failed:

failed to encode args[0]: unable to encode []main.Mood{"sad", "ok"} into text format for unknown type (OID 49659): cannot find encode plan

For an array pgx needs the array type's element codec, and the array type _mood is unknown to a plain connection. The same insert through the registered pool succeeded, and the read back was [sad ok]. Registration asks the server for mood and _mood and teaches the connection both. In general: scalars of an enum usually work, arrays of enums need registration, and registering always works.

Composite types

A composite type is a row type: create type address as (street text, city text, zip text). A column of that type holds a tuple. To use one from Go you give pgx a way to read and write its fields in order. The Address struct in the lab implements the interfaces pgx's composite codec looks for:

• ScanIndex(i int) any returns a pointer to the destination for field i when reading. The Zip field is a pgtype.Text, since the field can be NULL.
• ScanNull() error is called when the whole composite is NULL.
• Index(i int) any returns the value of field i when writing, and IsNull() bool says whether the whole value is NULL.

With those, the third block inserts an Address as a parameter and reads it back into another: {Street:1 Main St City:Springfield Zip:{String:12345 Valid:true}}. A NULL composite scanned into a *Address gave a nil pointer. The mapping is by position, so the struct's field order must match the type's, and changing the type in a migration means changing the Go code with it. If you do not want to implement the interfaces, scan the composite as a pgtype.CompositeFields or into []any, or avoid composite columns and use separate columns, which are easier to index and query; composite types are best for function arguments and results more than for table columns.

Domains

A domain is a base type with a constraint. create domain email672 as text check (value like '%@%') makes a type that is text to the client and has a rule on the server. The fourth block inserts a bad value and the server answers value for domain email672 violates check constraint "email672_check" with SQLSTATE 23514. No registration is needed: pgx treats the domain as its base type. This is a good use of the feature: the rule lives in the database next to the type, applies to every column of that type and to every client, and the Go side stays plain. Map the SQLSTATE and constraint name to a validation error as in handling database errors.

Ranges

PostgreSQL has built in range types (int4range, tsrange, daterange and others), and pgx maps them to pgtype.Range[T], a struct with Lower, Upper, LowerType and UpperType fields, where the types are Inclusive, Exclusive or Unbounded. The fifth block stores the hours 9 to 17 as [9,17) and reads back lower 9 inclusive and upper 17 exclusive. In SQL, hours @> 12 returned true. Ranges are a good fit for time slots and prices because the database can enforce non overlap with an exclusion constraint (exclude using gist (room with =, slot with &&)), a guarantee that is hard to implement correctly in application code. A custom range type created with create type ... as range needs LoadType and registration like other custom types.

Registering in practice

The registration function in the lab loads four names: mood, _mood, address and _address. The underscore prefix is PostgreSQL's convention for the array type of a type. Three practical points.

Order matters. A type that depends on another must be loaded after it: a composite with an enum field needs the enum registered first. LoadType itself loads the dependencies of composites in recent versions of pgx; check the version you use, and test with the types you have.

Names are schema qualified. LoadType(ctx, "address") finds the type through the search path. For a type in another schema, pass "app.address". Quote names that need it.

The registry is a snapshot. Registration happens at connect time. If a migration adds an enum value, nothing needs to change, since the type's identity is the same. If it creates a new type, existing pooled connections do not know it until they reconnect; either deploy migrations before the application, or reset the pool (close it and open another, or set a short MaxConnLifetime) after a migration that adds types. A new type used by code that is deployed first fails with the "unknown type" error from the second block.

A failure in AfterConnect makes the connection unusable and the pool retries, so a bad type name shows up as every query failing with the load error. Log it clearly, and test the pool against a database created from your migrations in CI.

Writing your own codec

When a type has no built in support, you can implement a pgtype.Codec, which describes the formats it supports, how to plan an encode and a decode for a given Go type, and how to scan values. That is more than most applications need. Two lighter options cover most cases: implement pgtype.TextValuer and pgtype.TextScanner on a Go type to send and read it as text, which is how a type like a PostGIS geometry or an ltree can be handled; or implement driver.Valuer and sql.Scanner and let pgx's fallback use them. Reach for a codec only for binary performance or complex nested types.

Arrays and null elements

Arrays deserve a closer look because they are the most common custom shaped value after JSON. A PostgreSQL array has a type per element type, and elements can be NULL. pgx maps a text[] to []string for the common case, which cannot hold a NULL element: scanning an array that contains one fails. For arrays that may contain NULLs use []*string or pgtype.FlatArray[pgtype.Text], which keeps a Valid flag per element. Multidimensional arrays need pgtype.Array[T], which carries the dimensions, and are rare enough that most code should use one dimension and a separate column or table for the second.

Arrays of custom types, such as []Address for an address[] column, need the array type registered (_address) as the lab does, and the element type implementing the composite interfaces. If you only need to query with a set of values, do not send an array of a custom type: send an array of a built in type ($1::int[]) and compare with any($1), which works without registration and is the usual way to express where id in (...).

Nullable and optional values in custom types

A custom type brings the same NULL questions as a built in one. For an enum column that allows NULL, scan into a *Mood or a pgtype.Text: a plain Mood cannot represent NULL and returns an error. For a composite column, a nil *Address is the natural NULL, as the lab showed. For fields inside a composite, use pgtype wrappers as the lab does with Zip, because a composite's field can be NULL independently of the composite itself. Decide for each type whether NULL is allowed at all, and put not null in the schema where it is not: it turns a class of scan errors into a constraint at write time.

Testing the registration

Registration bugs appear as run time errors in production, so cover them in tests. A good integration test creates a database from your real migrations, builds the pool the way the application does, with the same AfterConnect, and round trips one value of each custom type: write it, read it back, compare. Add a test that connects with a plain configuration and shows the type fails, if you want a regression guard for the "unknown type" failure mode. Run it against the PostgreSQL version you deploy. The same test is the place to check that a migration that adds a type is followed by a registration change, since forgetting one is the usual cause of an outage after a schema change that "only added an enum".

Keep the list of types to register next to the migrations that create them, or generate it from a query of the catalog at start-up for every user defined enum, composite and domain in your schema (select typname from pg_type where typnamespace = 'public'::regnamespace and typtype in ('e', 'c')), registering each with its array type. Generating the list removes the maintenance and adds a start-up query per connection, which is acceptable for a pool of tens of connections and not for one that churns constantly.

Upgrading and versioning

Types change over the life of a service, and each change has a different cost for the clients. Adding a value to an enum with alter type ... add value is safe for running clients, and the new value needs no registration change. Renaming a value or removing one is not possible without rebuilding the type. Adding a field to a composite is a type change that every client with a hard coded field order must follow in the same release, which is a strong argument for using separate columns instead. Changing a domain's check can reject rows that already exist unless you add it as not valid first and validate later, as in zero downtime migrations. Plan type changes with the same care as column changes, and test the Go side against the migrated schema before the migration runs in production.

Choosing types

A custom type is a decision to put a rule or a shape in the database. Use an enum for a small, stable set of values and accept that removing a value is hard; a lookup table with a foreign key is easier to change and to extend. Use a domain for a rule shared by several columns. Use ranges with exclusion constraints for scheduling. Be cautious with composite columns: they cannot be indexed on their fields easily and are awkward in where clauses. And remember the cost: every custom type is something every client must be told about. The registry API is documented in the pgtype package, and the types themselves in the PostgreSQL manual under enumerated types, composite types and range types.