pgx Queries Scanning and Collecting Rows to Structs

Reading rows is where most database code is repetitive: open the rows, defer close, loop, scan into variables in the right order, append to a slice, check rows.Err(). pgx v5 turns all of it into one call with pgx.CollectRows, plus a set of functions that say how to read a row into a struct, a scalar or a map. This article runs each against PostgreSQL 18.6 on Go 1.25.6 and records what they do, including the errors you will meet.

It belongs to the database cornerstone, follows the pgx tour and pairs with scanning rows into structs with database/sql and nested structs and joins.

The idea

CollectRows(rows, fn) takes the pgx.Rows from a query and a RowToFunc[T], a function that reads the current row into a T. It calls the function for every row, builds a []T, closes the rows, and returns any error from the query or the loop. You do not close the rows and you do not call rows.Err(). The row functions come from pgx:

package main

import (
	"context"
	"errors"
	"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"

type User struct {
	ID      int64
	Name    string
	Email   *string // nullable
	Created time.Time
	Tags    []string
}

// Extra holds a column the struct does not have a field for, to show the error that names it.
type Short struct {
	ID   int64
	Name string
}

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

func main() {
	ctx := context.Background()
	pool, err := pgxpool.New(ctx, dsn)
	must(err)
	defer pool.Close()
	for _, q := range []string{
		"drop table if exists users667",
		"create table users667 (id bigint primary key, name text not null, email text, created timestamptz not null default '2026-01-01', tags text[] not null default '{}')",
		"insert into users667 (id, name, email, tags) values (1, 'ada', '[email protected]', '{admin,dev}'), (2, 'alan', null, '{}'), (3, 'grace', '[email protected]', '{dev}')",
	} {
		_, err := pool.Exec(ctx, q)
		must(err)
	}

	// 1. By name: columns are matched to fields by name, in any order.
	rows, _ := pool.Query(ctx, "select name, id, tags, created, email from users667 order by id")
	users, err := pgx.CollectRows(rows, pgx.RowToStructByName[User])
	must(err)
	fmt.Printf("1. RowToStructByName, columns in a different order than the struct: %d users, user 2 has a nil email: %v\n", len(users), users[1].Email == nil)

	// 2. By position: the columns must be in field order.
	rows, _ = pool.Query(ctx, "select id, name, email, created, tags from users667 order by id")
	byPos, err := pgx.CollectRows(rows, pgx.RowToStructByPos[User])
	must(err)
	fmt.Printf("2. RowToStructByPos: %d users, first is %s\n", len(byPos), byPos[0].Name)

	// 3. A column the struct lacks.
	rows, _ = pool.Query(ctx, "select id, name, email from users667")
	_, err = pgx.CollectRows(rows, pgx.RowToStructByName[Short])
	fmt.Println("3. a column with no field:", err)

	// 4. A field the query lacks.
	rows, _ = pool.Query(ctx, "select id from users667")
	_, err = pgx.CollectRows(rows, pgx.RowToStructByName[Short])
	fmt.Println("4. a field with no column:", err)
	rows, _ = pool.Query(ctx, "select id from users667")
	loose, err := pgx.CollectRows(rows, pgx.RowToStructByNameLax[Short])
	fmt.Printf("   RowToStructByNameLax accepts it: %d rows, Name left empty: %v, err %v\n", len(loose), loose[0].Name == "", err)

	// 5. One column into a slice of scalars, and a map.
	rows, _ = pool.Query(ctx, "select name from users667 order by id")
	names, err := pgx.CollectRows(rows, pgx.RowTo[string])
	must(err)
	fmt.Println("5. RowTo[string]:", names)
	rows, _ = pool.Query(ctx, "select id, name from users667 order by id")
	byID, err := pgx.CollectRows(rows, pgx.RowToMap)
	must(err)
	fmt.Printf("   RowToMap: %v\n", byID[0])

	// 6. Exactly one row: CollectOneRow, with its two errors.
	rows, _ = pool.Query(ctx, "select id, name, email, created, tags from users667 where id = 2")
	one, err := pgx.CollectOneRow(rows, pgx.RowToStructByPos[User])
	fmt.Printf("6. CollectOneRow: %s, err %v\n", one.Name, err)
	rows, _ = pool.Query(ctx, "select id from users667 where id = 99")
	_, err = pgx.CollectOneRow(rows, pgx.RowTo[int64])
	fmt.Println("   no row:", err, "| pgx.ErrNoRows:", errors.Is(err, pgx.ErrNoRows))
	rows, _ = pool.Query(ctx, "select id from users667")
	_, err = pgx.CollectOneRow(rows, pgx.RowTo[int64])
	fmt.Println("   several rows: err", err)
	rows, _ = pool.Query(ctx, "select id from users667")
	first, err := pgx.CollectExactlyOneRow(rows, pgx.RowTo[int64])
	fmt.Println("   CollectExactlyOneRow with three rows:", first, err, "| ErrTooManyRows:", errors.Is(err, pgx.ErrTooManyRows))

	// 7. Streaming with ForEachRow, without building a slice.
	rows, _ = pool.Query(ctx, "select id, name from users667 order by id")
	var id int64
	var name string
	count := 0
	_, err = pgx.ForEachRow(rows, []any{&id, &name}, func() error {
		count++
		return nil
	})
	must(err)
	fmt.Printf("7. ForEachRow visited %d rows without allocating a slice; last was %s\n", count, name)

	// 8. Reading a value of an unsupported shape.
	var n pgtype.Numeric
	err = pool.QueryRow(ctx, "select 'text'::text").Scan(&n)
	fmt.Println("8. scanning text into a numeric:", err)
}

1. RowToStructByName, columns in a different order than the struct: 3 users, user 2 has a nil email: true
2. RowToStructByPos: 3 users, first is ada
3. a column with no field: struct doesn't have corresponding row field email
4. a field with no column: cannot find field Name in returned row
   RowToStructByNameLax accepts it: 3 rows, Name left empty: true, err <nil>
5. RowTo[string]: [ada alan grace]
   RowToMap: map[id:1 name:ada]
6. CollectOneRow: alan, err <nil>
   no row: no rows in result set | pgx.ErrNoRows: true
   several rows: err <nil>
   CollectExactlyOneRow with three rows: 0 too many rows in result set | ErrTooManyRows: true
7. ForEachRow visited 3 rows without allocating a slice; last was grace
8. scanning text into a numeric: can't scan into dest[0] (col: text): text is not a number

By name

RowToStructByName[User] matches each column in the result to a struct field of the same name, ignoring case and treating _ as optional, so a column created_at fills CreatedAt (or a field tagged db:"created_at"). The first block selects the columns in a different order from the struct's fields, name, id, tags, created, email, and it works: three users came back. The Email field is a *string and user 2 has a NULL email, so it arrived as nil. Nullable columns need a pointer or a pgtype wrapper in the struct, or the scan fails.

Tags let you decouple names: a field Created time.Time with the tag db:"created_at" reads the column created_at. Use db:"-" to skip a field. This is the function to use by default, since it survives column reordering and added columns that the struct also has, and it fails loudly when the two disagree.

By position

RowToStructByPos[User] fills fields in order: the first column goes to the first field, and so on. The second block selects id, name, email, created, tags, the struct's field order, and it worked. It is a little faster and a lot more fragile: reordering the select list or adding a field silently shifts values into the wrong fields, or fails with a type error if you are lucky. Use it only with queries written right beside the struct.

When columns and fields disagree

The third and fourth blocks show the two mismatches, because their error messages are worth recognising.

A column with no field, selecting email into a struct that has only ID and Name, failed with struct doesn't have corresponding row field email. The struct must have a field for every column. That is a useful guard: add a column to a query and forget the struct, and you find out on the first run rather than by silently dropping data.

A field with no column, selecting only id into the struct with ID and Name, failed with cannot find field Name in returned row. There is a variant for this case: RowToStructByNameLax allows the struct to have more fields than the query has columns. It returned three rows, with Name left empty. It does not relax the other direction, a column with no field still fails. Use the lax version for partial selects into a shared struct, and know that you gave up the check that every field was filled.

Scalars, maps and one row

RowTo[string] reads a single column into a scalar and collected ["ada", "alan", "grace"]: the way to read a list of ids or names. RowToMap reads a row into a map[string]any, useful for exploration and for code that does not know the columns, at the cost of types: values come back in pgx's default Go types.

For queries that should return exactly one row there are two functions with different strictness, and the difference matters:

• CollectOneRow returns the first row and ignores any others. In the lab, a query that returned three ids gave the first with no error. If your where clause was supposed to match one row and matches three because of a bug, this function hides it.
• CollectExactlyOneRow returns pgx.ErrNoRows for none and pgx.ErrTooManyRows for more than one. The sixth block's query with three rows returned too many rows in result set, and errors.Is(err, pgx.ErrTooManyRows) was true.

Both return pgx.ErrNoRows when there is no row, the same value as QueryRow(...).Scan gives, so one errors.Is check covers all of them, as in handling ErrNoRows. Prefer the exact version for lookups by a unique key; it turns a violated assumption into an error.

Streaming

CollectRows builds a slice of the whole result, which is wrong for a million rows. ForEachRow calls a function for each row, scanning into targets you provide, without building anything. The seventh block passed pointers to an id and a name and a callback that ran three times; the callback saw the current values in those variables. Use it for exports, for streaming into a response, and for aggregating in Go. For fine control, or when you need to stop early, the manual loop is still there: for rows.Next() { rows.Scan(...) }, with defer rows.Close() and a check of rows.Err().

Errors from scanning

The eighth block scans text into a pgtype.Numeric and gets can't scan into dest[0] (col: text): text is not a number: type mismatches are reported with the column name and a reason, and they happen at scan time, so a query that returns a different type than the struct expects fails on the first row rather than on the hundredth. Wrap errors with the query's name, as in database errors, so the log says which query failed.

Habits

1. Use CollectRows and RowToStructByName for lists, CollectExactlyOneRow for unique lookups, RowTo[T] for single columns.
2. Give nullable columns pointer or pgtype fields.
3. Select only the columns the struct needs, by name, rather than select *: the by name functions then keep working when someone adds a column to the table.
4. Put the SQL constant next to the struct it fills, so a change to one is seen with the other.
5. Use ForEachRow or a manual loop for results too large to hold.
6. Test each query against a real database with the struct, since the mapping errors above are run time errors.

The functions are documented in the pgx package reference, the struct mapping rules in the pgx project documentation, and generated alternatives that check queries at build time are covered in sqlc.