pgx and JSONB Arrays UUIDs and Ranges in Go Programs

PostgreSQL's richer types are a large part of why people choose it, and pgx is the Go driver that supports them best. This article covers the five you will use most outside the basics: jsonb, integer arrays, uuid, range types and numeric. It runs on PostgreSQL 18.6 from Go 1.25.6 with pgx v5.11.0, shows how each maps to Go, and goes through the failures that follow from the mismatches: a NULL that is not a JSON null, a malformed id, a decimal that must not be a float.

It belongs to the database cornerstone. Registering your own types is in custom types, the SQL side of JSON in JSON columns with Postgres JSONB, and arrays in arrays and custom types.

package main

import (
	"context"
	"encoding/json"
	"fmt"
	"log"
	"time"

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

type Profile struct {
	Theme string   `json:"theme"`
	Langs []string `json:"langs"`
	Beta  bool     `json:"beta"`
}

func main() {
	ctx := context.Background()
	pool, err := pgxpool.New(ctx, dsn)
	must(err)
	defer pool.Close()
	for _, q := range []string{
		"drop table if exists docs673",
		"create table docs673 (id uuid primary key default gen_random_uuid(), name text not null, profile jsonb not null default '{}', tags int[] not null default '{}', slot tstzrange, price numeric(12,4))",
	} {
		_, err := pool.Exec(ctx, q)
		must(err)
	}

	// 1. JSONB from a Go struct, and back.
	p := Profile{"dark", []string{"go", "sql"}, true}
	var id pgtype.UUID
	must(pool.QueryRow(ctx, "insert into docs673 (name, profile) values ($1, $2) returning id", "ada", p).Scan(&id))
	var back Profile
	must(pool.QueryRow(ctx, "select profile from docs673 where id = $1", id).Scan(&back))
	fmt.Printf("1. jsonb struct round trip: %+v\n", back)

	// 2. Query inside the document, and ask for one field only.
	var theme string
	must(pool.QueryRow(ctx, "select profile->>'theme' from docs673 where profile @> $1", `{"beta": true}`).Scan(&theme))
	fmt.Println("2. profile @> '{\"beta\": true}' found a document whose theme is", theme)
	var n int
	must(pool.QueryRow(ctx, "select count(*) from docs673 where profile ? 'langs'").Scan(&n))
	fmt.Println("   documents that have a langs key:", n)

	// 3. Partial update of one key.
	_, err = pool.Exec(ctx, "update docs673 set profile = jsonb_set(profile, '{theme}', to_jsonb($1::text)) where id = $2", "light", id)
	must(err)
	must(pool.QueryRow(ctx, "select profile->>'theme' from docs673 where id = $1", id).Scan(&theme))
	fmt.Println("3. jsonb_set changed one key, theme is now", theme)

	// 4. Dynamic shapes: scan into any, and into json.RawMessage.
	var anyv any
	must(pool.QueryRow(ctx, "select profile from docs673 where id = $1", id).Scan(&anyv))
	var raw json.RawMessage
	must(pool.QueryRow(ctx, "select profile from docs673 where id = $1", id).Scan(&raw))
	fmt.Printf("4. into any: %T; into json.RawMessage: %s\n", anyv, raw)

	// 5. NULL in JSON: a SQL NULL and a JSON null are different things.
	_, err = pool.Exec(ctx, "alter table docs673 alter column profile drop not null")
	must(err)
	_, err = pool.Exec(ctx, "insert into docs673 (name, profile) values ('sql null', null), ('json null', 'null'::jsonb)")
	must(err)
	rows, _ := pool.Query(ctx, "select name, profile is null as sql_null, coalesce(jsonb_typeof(profile) = 'null', false) as json_null from docs673 where name like '%null' order by name")
	type row struct {
		Name     string
		SQLNull  bool
		JSONNull bool
	}
	got, err := pgx.CollectRows(rows, pgx.RowToStructByPos[row])
	must(err)
	fmt.Printf("5. %+v\n", got)

	// 6. UUIDs: pgtype.UUID, [16]byte and text all work.
	var asBytes [16]byte
	must(pool.QueryRow(ctx, "select id from docs673 where name = 'ada'").Scan(&asBytes))
	var asText string
	must(pool.QueryRow(ctx, "select id::text from docs673 where name = 'ada'").Scan(&asText))
	fmt.Printf("6. uuid as pgtype.UUID valid=%v, as [16]byte first byte %d, as text of length %d\n", id.Valid, asBytes[0], len(asText))
	var bad pgtype.UUID
	err = bad.Scan("not-a-uuid")
	fmt.Println("   a malformed uuid:", err)

	// 7. Integer arrays, and ANY for an IN list.
	_, err = pool.Exec(ctx, "update docs673 set tags = $1 where id = $2", []int32{3, 5, 8}, id)
	must(err)
	var tags []int32
	must(pool.QueryRow(ctx, "select tags from docs673 where id = $1", id).Scan(&tags))
	var found int
	must(pool.QueryRow(ctx, "select count(*) from docs673 where id = any($1)", []pgtype.UUID{id}).Scan(&found))
	fmt.Printf("7. int[] round trip %v; id = any($1) with a uuid slice found %d row\n", tags, found)

	// 8. A timestamp range.
	from := time.Date(2026, 3, 1, 9, 0, 0, 0, time.UTC)
	slot := pgtype.Range[pgtype.Timestamptz]{
		Lower: pgtype.Timestamptz{Time: from, Valid: true, InfinityModifier: pgtype.Finite}, Upper: pgtype.Timestamptz{Time: from.Add(2 * time.Hour), Valid: true},
		LowerType: pgtype.Inclusive, UpperType: pgtype.Exclusive, Valid: true,
	}
	_, err = pool.Exec(ctx, "update docs673 set slot = $1 where id = $2", slot, id)
	must(err)
	var length string
	must(pool.QueryRow(ctx, "select (upper(slot) - lower(slot))::text from docs673 where id = $1", id).Scan(&length))
	fmt.Println("8. tstzrange stored; its length is", length)

	// 9. numeric keeps its digits.
	_, err = pool.Exec(ctx, "update docs673 set price = $1 where id = $2", "19.9999", id)
	must(err)
	var price pgtype.Numeric
	must(pool.QueryRow(ctx, "select price from docs673 where id = $1", id).Scan(&price))
	v, _ := price.Value()
	fmt.Printf("9. numeric(12,4) 19.9999 read back as %v (exponent %d, int %v)\n", v, price.Exp, price.Int)
}

1. jsonb struct round trip: {Theme:dark Langs:[go sql] Beta:true}
2. profile @> '{"beta": true}' found a document whose theme is dark
   documents that have a langs key: 1
3. jsonb_set changed one key, theme is now light
4. into any: map[string]interface {}; into json.RawMessage: {"beta": true, "langs": ["go", "sql"], "theme": "light"}
5. [{Name:json null SQLNull:false JSONNull:true} {Name:sql null SQLNull:true JSONNull:false}]
6. uuid as pgtype.UUID valid=true, as [16]byte first byte 42, as text of length 36
   a malformed uuid: cannot parse UUID not-a-uuid
7. int[] round trip [3 5 8]; id = any($1) with a uuid slice found 1 row
8. tstzrange stored; its length is 02:00:00
9. numeric(12,4) 19.9999 read back as 19.9999 (exponent -4, int 199999)

JSONB

jsonb stores a document in a binary form that is indexed and queryable. pgx encodes a Go value with encoding/json when you pass it as a parameter for a jsonb column, and decodes with encoding/json when you scan. The first block inserts a Profile struct and reads it back into another: {Theme:dark Langs:[go sql] Beta:true}, unchanged. Maps, slices, json.RawMessage and anything with a json marshaller work the same way. Pass a string or []byte and pgx treats it as already JSON; pass a struct and it marshals. Be careful passing a Go string meaning "a JSON string value": it will be taken as the JSON text, so wrap it with json.Marshal if you want a quoted string.

The best use of jsonb is in SQL, not in Go. The second block asks the database: profile @> '{"beta": true}' found the document that contains that fragment, and profile->>'theme' returned the theme as text without decoding the document in Go. profile ? 'langs' counted documents with a key. A GIN index on the column makes containment and key queries fast. The third block changed one key with jsonb_set(profile, '{theme}', to_jsonb($1::text)): the document was updated in place and nothing was read into Go and written back, which also avoids a lost update when two writers touch different keys.

When the shape is not known, scan into any (a map[string]interface{} here, as the fourth block prints) or into json.RawMessage, which gives the bytes as stored: {"beta": true, "langs": ["go", "sql"], "theme": "light"}. Note that jsonb normalises: keys come back sorted and whitespace is gone, so do not rely on key order or on byte for byte equality.

Use jsonb for data that varies in shape, a sparse set of attributes or a payload you store but rarely query, and ordinary columns for everything you filter, join or constrain on. A table that is one data jsonb column has given up the database's checks.

SQL NULL and JSON null

The fifth block shows a trap in nullable JSON columns. The column allows NULL, and two rows were inserted: one with a SQL NULL and one with the JSON value null ('null'::jsonb). They are different. profile is null is true only for the first, and jsonb_typeof(profile) = 'null' only for the second, as the output shows: {Name:json null SQLNull:false JSONNull:true} and {Name:sql null SQLNull:true JSONNull:false}. A comparison like profile = 'null'::jsonb returns SQL NULL for the first row, which broke the first version of this lab with cannot scan NULL into *bool; wrap such tests in coalesce.

In Go, scanning a SQL NULL into a struct or map leaves it nil or zero; scanning a JSON null does the same, so Go cannot tell them apart unless you scan into *json.RawMessage or a pgtype wrapper. Decide which you mean and enforce it: make the column not null with a default of '{}', and the question disappears.

UUIDs

The sixth block reads one uuid three ways. pgtype.UUID has Bytes [16]byte and a Valid flag, and it is what to use for nullable ids. A plain [16]byte works for non-null values: the first byte printed. And scanning into a string works if the SQL casts with id::text, giving the 36 character form. A malformed string parsed with pgtype.UUID.Scan returned cannot parse UUID not-a-uuid, which is the error to catch at the API boundary when a client sends a bad id: parse early and return a 400.

Most Go code uses github.com/google/uuid, whose uuid.UUID is also a [16]byte, and pgx can scan into it directly, so you rarely convert by hand. Generate ids in the database (gen_random_uuid(), or uuidv7() in PostgreSQL 18, which is time ordered and indexes much better than random v4, as UUIDs, ULIDs and serial IDs measured) or in Go, and prefer the database for defaults so every writer is consistent.

Arrays

Integer arrays map to Go slices: the seventh block wrote []int32{3, 5, 8} to an int[] column and read [3 5 8] back. Use []int32 for int4[] and []int64 for int8[]; the element width must match, or pgx reports an encoding error. NULL elements need pointer elements or pgtype wrappers.

The most useful array trick is the IN list. Instead of building where id in ($1, $2, $3, ...) with a variable number of placeholders, pass one slice: where id = any($1) with a []pgtype.UUID found the one row in the lab. One statement text means one cached plan, there is no limit on the number of values beyond the parameter size, and no string building. It is the same pattern as dynamic queries without string concatenation.

Ranges

PostgreSQL ranges represent an interval as one value. pgx maps them to pgtype.Range[T], here Range[pgtype.Timestamptz], with lower and upper bounds that can be inclusive, exclusive or unbounded. The eighth block stored a two hour slot from 09:00 and asked the database for its length: 02:00:00. Because the range is one value, the database can enforce rules over it: an exclusion constraint such as exclude using gist (room with =, slot with &&) rejects overlapping bookings, which is the right way to prevent double booking, and is difficult and racy to do in Go. Use half open ranges ([start, end)) so that adjacent slots do not overlap.

numeric and money

The ninth block is the one with consequences for money. numeric(12,4) stored 19.9999, and pgx read it back as pgtype.Numeric with the integer 199999 and exponent -4: exact. Scanning a numeric into a float64 would work and would lose exactness for values that binary floating point cannot represent, which includes most decimal fractions. For money, either keep integer minor units (cents) in a bigint, or use numeric with pgtype.Numeric or a decimal library registered with pgx, and never compute with floats. Sending a price as a Go string ("19.9999") is also safe: the server parses it exactly. See decimal and money types.

Habits

1. Query inside JSON in SQL, update keys with jsonb_set, and keep filterable fields in real columns.
2. Make JSON columns not null with a default so SQL NULL and JSON null do not both exist.
3. Parse UUIDs at the edge, use pgtype.UUID for nullable ones, and prefer database generated, time ordered ids.
4. Match array element widths to the column type, and pass slices with = any($1) instead of building IN lists.
5. Use ranges and exclusion constraints for intervals that must not overlap.
6. Never use floats for money.

The mappings are documented in the pgtype package, and the types and operators in the PostgreSQL manual's chapters on JSON, arrays and range types.