Arrays and Custom Types in Go With Postgres and pgx Codecs

PostgreSQL has first-class arrays, and Go has slices. The two fit together neatly in pgx. This is one of the pleasant surprises of moving from the standard database/sql interface, which has no portable story for arrays, to the native driver. This article covers arrays, the query operators that go with them and custom types such as enums, on Go 1.25.6 and PostgreSQL 18.

Arrays as slices

package main

import (
	"context"
	"fmt"
	"log"

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

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

type Status string

const (
	Draft     Status = "draft"
	Published Status = "published"
)

type Post struct {
	ID     int      `db:"id"`
	Title  string   `db:"title"`
	Tags   []string `db:"tags"`
	Scores []int32  `db:"scores"`
	Status Status   `db:"status"`
}

func main() {
	ctx := context.Background()
	pool, err := pgxpool.New(ctx, dsn)
	if err != nil {
		log.Fatal(err)
	}
	defer pool.Close()
	_, err = pool.Exec(ctx, `drop table if exists p596; drop type if exists status596;
		create type status596 as enum ('draft', 'published');
		create table p596 (id int primary key, title text, tags text[], scores int[], status status596)`)
	if err != nil {
		log.Fatal(err)
	}

	// Slices go in as arrays and come back as slices.
	_, err = pool.Exec(ctx, `insert into p596 values ($1, $2, $3, $4, $5)`,
		1, "Go and SQL", []string{"go", "sql"}, []int32{5, 9}, Published)
	fmt.Println("insert:", err)
	_, _ = pool.Exec(ctx, `insert into p596 values (2, 'Untagged', '{}', null, 'draft')`)

	rows, _ := pool.Query(ctx, `select id, title, tags, scores, status from p596 order by id`)
	posts, err := pgx.CollectRows(rows, pgx.RowToStructByName[Post])
	fmt.Printf("collected: %+v err=%v\n", posts, err)
	fmt.Printf("empty array is non-nil: %v, NULL array is nil: %v\n", posts[1].Tags != nil && len(posts[1].Tags) == 0, posts[1].Scores == nil)

	// Array operators in SQL.
	var n int
	_ = pool.QueryRow(ctx, `select count(*) from p596 where tags @> $1`, []string{"go"}).Scan(&n)
	fmt.Println("tags @> {go}:", n)
	_ = pool.QueryRow(ctx, `select count(*) from p596 where $1 = any(tags)`, "sql").Scan(&n)
	fmt.Println("'sql' = any(tags):", n)
	_ = pool.QueryRow(ctx, `select count(*) from p596 where id = any($1)`, []int{1, 2, 3}).Scan(&n)
	fmt.Println("id = any($1) with []int{1,2,3}:", n)

	// An enum value that the type does not allow is rejected by the database.
	_, err = pool.Exec(ctx, `insert into p596 (id, title, status) values (3, 'x', $1)`, Status("archived"))
	fmt.Printf("invalid enum value: %.70s\n", fmt.Sprint(err))
	_, _ = pool.Exec(ctx, `drop table p596; drop type status596`)
}

go run . 2>&1

insert: <nil>
collected: [{ID:1 Title:Go and SQL Tags:[go sql] Scores:[5 9] Status:published} {ID:2 Title:Untagged Tags:[] Scores:[] Status:draft}] err=<nil>
empty array is non-nil: true, NULL array is nil: true
tags @> {go}: 1
'sql' = any(tags): 1
id = any($1) with []int{1,2,3}: 2
invalid enum value: ERROR: invalid input value for enum status596: "archived" (SQLSTATE 22

The table has a tags text[], a scores int[] and a status column of an enum type. The program writes []string{"go", "sql"} and []int32{5, 9} as parameters of an insert, with no wrapper and no pq.Array:

pool.Exec(ctx, `insert into p596 values ($1, $2, $3, $4, $5)`,
	1, "Go and SQL", []string{"go", "sql"}, []int32{5, 9}, Published)

pgx asks the server what type each parameter is, sees text[], int4[] and the enum, and encodes the Go values accordingly. Scanning goes the other way, into struct fields of type []string and []int32, with pgx.CollectRows and RowToStructByName. The program prints the collected rows with the tags and scores intact.

Match element types to the database type: int[] is 32-bit and maps to []int32; bigint[] maps to []int64; a plain []int works for parameters, since pgx converts, but check the range. For numeric[] use a decimal type, as discussed in money in Go.

NULL and empty are different

PostgreSQL distinguishes a NULL array from an empty one, and pgx preserves it. The second row has tags = '{}' and scores = null. The output states: empty array is non-nil: true, NULL array is nil: true. A scanned empty array is a non-nil, zero-length slice; a NULL is a nil slice. In Go, len(x) == 0 is true for both, so code that only checks the length cannot tell them apart, and the distinction rarely matters. Where it does, check x == nil. When you marshal to JSON, nil becomes null and an empty slice becomes []; initialise slices that should serialise as empty arrays.

Writing a nil slice stores NULL, and writing an empty slice stores {}. A not null default '{}' column removes the question.

Queries on arrays

Arrays shine when you query them in SQL. Three forms cover most uses.

Membership of one value: where $1 = any(tags). The program checks 'sql' = any(tags) and gets 1 row.

Containment of several values: where tags @> $1 with a Go []string. @> means "contains all of these". The program passes []string{"go"} and counts 1 row. The mirror operators are <@ (is contained by) and && (overlaps, any in common).

Passing a list as a parameter: where id = any($1) with a []int{1, 2, 3}. This replaces the IN (?, ?, ?) list that you would have to expand by hand with database/sql: the whole slice is one parameter, so the query text is constant, the placeholder count never changes, and the statement can be prepared once. The program matches 2 rows. It is the single best reason to use PostgreSQL arrays from Go, even if no column is an array.

Index array columns with GIN for @>, <@ and &&: create index on t using gin (tags).

Enums and custom string types

A PostgreSQL enum is a type with a fixed set of labels. The Go side is a defined string type:

type Status string

const (
	Draft     Status = "draft"
	Published Status = "published"
)

pgx encodes a Status as text and the server converts it to the enum, then rejects labels outside the set. The program inserts Status("archived") and the error begins:

ERROR: invalid input value for enum status596: "archived"

The database is the guard. Reading back, an enum column scans into the Status string type through the struct field. Notes on enums: adding a label is cheap (alter type ... add value), removing or renaming one is awkward, and ordering follows declaration order. Many teams prefer a text column with a check (status in (...)) constraint for easier change, or a lookup table with a foreign key when the values carry data. Pick enums for small, stable sets.

Add a String() or validation method to the Go type, or keep the constants and a Valid() function, so invalid values are caught before the round trip.

Composite types and ranges

pgx also supports composite types, ranges (int4range, tstzrange) and hstore through codecs. Built-in types need no setup. For a user-defined composite or enum array, register the type on the connection, so that pgx learns its OID, using conn.LoadType in an AfterConnect hook on the pool config:

cfg.AfterConnect = func(ctx context.Context, c *pgx.Conn) error {
	t, err := c.LoadType(ctx, "status596")
	if err != nil {
		return err
	}
	c.TypeMap().RegisterType(t)
	return nil
}

This code is shown for the shape and was not run; the lab uses the enum as text and needs no registration. You need it when scanning arrays of enums or composites into typed slices.

With database/sql

Through the standard pgx/stdlib adapter, arrays work if you wrap them with pgtype.FlatArray or implement the Scanner and Valuer interfaces. The old lib/pq approach was pq.Array(&x). The native pgx interface is simpler, which is a reason to use it directly for PostgreSQL code. Portable code that must also run on MySQL cannot use arrays at all; use a join table or JSON there.

Arrays and NULL elements

An element inside an array can itself be NULL: '{a,NULL,c}'::text[]. A Go []string cannot hold that, and scanning such an array into it fails. Use []*string or []pgtype.Text when elements may be NULL, as in scanning NULL. Better, avoid NULL elements in the schema: add check (array_position(tags, null) is null) or normalise in the writer. A related trap is comparing with NULL inside any(): x = any('{1,NULL}') is NULL, not false, when x is not in the list, which breaks not conditions.

Cost of large parameter arrays

= any($1) with ten thousand IDs is one parameter on the wire, which is compact, and PostgreSQL plans it as a hash lookup in recent versions. It is far better than ten thousand placeholders. Past a hundred thousand elements, the array itself becomes memory the server must parse; use a temporary table and COPY, or process in batches of a few thousand. Measure with explain analyze and the timing tools in the benchmark guide before and after.

When arrays are the wrong tool

An array column is a denormalised list. It cannot carry foreign keys, so referential integrity is on you. Updating one element rewrites the whole array. Counting or aggregating across elements needs unnest. If the elements have attributes or are shared between rows, use a separate table. Use arrays for small, bounded lists owned by the row: tags, flags, a handful of IDs.

Testing

Test arrays against a real PostgreSQL: round trip a value, the empty array, the NULL array and an array with a NULL element. Test the operators you rely on, with rows that should and should not match, and check the invalid enum value is rejected. The container setup is in PostgreSQL tests, and pgx's own docs have the type mapping table. The PostgreSQL manual covers array functions and operators and enumerated types.