sqlc JSON Arrays and Enums in Postgres Mapped to Go Types

PostgreSQL has three column kinds that cause most of the type questions when people adopt sqlc: jsonb, enums and arrays. Each maps to something reasonable by default, and each has one sharp edge. This article runs a table that uses all three on PostgreSQL 18.6 with sqlc v1.30.0, pgx v5.11.0 and Go 1.25.6, shows the Go types that come out, and records the failure of the one that does not work out of the box.

It is part of the sqlc cluster that starts at the tutorial. On the pgx side, the same questions are covered in the jsonb and UUID article, and the topic belongs to the database cornerstone.

The table

create type order_status as enum ('new', 'paid', 'shipped', 'cancelled');
create table orders (
    id     serial primary key,
    status order_status not null default 'new',
    prior  order_status,
    meta   jsonb not null,
    raw    jsonb
);

An enum order_status, a nullable second use of it (prior), a non null jsonb column with a known shape (meta) and a nullable one with no fixed shape (raw). The queries insert, filter on a JSON field, update, and filter on a list of statuses:

-- name: Place :one
insert into orders (status, meta, raw) values ($1, $2, $3) returning *;

-- name: BySKU :many
select * from orders where meta->>'sku' = @sku::text order by id;

-- name: Ship :one
update orders set prior = status, status = 'shipped' where id = $1 returning *;

-- name: ByStatus :many
select id, status from orders where status = any(@statuses::order_status[]) order by id;

-- name: ByStatusText :many
select id, status from orders where status::text = any(@statuses::text[]) order by id;

Typing jsonb with an override

By default a jsonb column is []byte in the generated struct. That is accurate and unhelpful: every read ends in json.Unmarshal, and every write in json.Marshal. An override makes the column a Go type of yours, and sqlc's pgx code marshals it with encoding/json:

version: "2"
sql:
  - engine: postgresql
    schema: schema.sql
    queries: query.sql
    gen:
      go:
        package: main
        out: .
        sql_package: pgx/v5
        overrides:
          - column: "orders.meta"
            go_type: "example.test/lab/types.Meta"

package types

// Meta is what the orders.meta jsonb column holds; sqlc marshals it with encoding/json.
type Meta struct {
	SKU   string   `json:"sku"`
	Gift  bool     `json:"gift,omitempty"`
	Notes []string `json:"notes,omitempty"`
}

The override is for one column (orders.meta), with go_type naming a type in another package, as the overrides article explains, since a type in the generated package would create an import cycle.

The nullable column raw has no override, so it stays []byte. That is the right choice for JSON with no fixed shape (a webhook payload, a third party's response): you hold the bytes and decode the parts you need.

The run

package main

import (
	"context"
	"fmt"
	"log"

	"example.test/lab/types"
	"github.com/jackc/pgx/v5/pgxpool"
)

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

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()
	q := New(pool)

	fmt.Println("1. generated enum constants:", OrderStatusNew, OrderStatusPaid, OrderStatusShipped, OrderStatusCancelled)
	o, err := q.Place(ctx, PlaceParams{Status: OrderStatusPaid, Meta: types.Meta{SKU: "A-1", Gift: true, Notes: []string{"wrap it"}}, Raw: []byte(`{"anything": [1, 2, 3]}`)})
	must(err)
	fmt.Printf("2. Order fields: Status %T, Prior %T, Meta %T, Raw %T\n   meta read back as a struct: %+v\n   raw jsonb comes back as bytes: %s\n", o.Status, o.Prior, o.Meta, o.Raw, o.Meta, o.Raw)
	fmt.Println("   prior is NULL:", !o.Prior.Valid)

	_, err = q.Place(ctx, PlaceParams{Status: OrderStatus("refunded"), Meta: types.Meta{SKU: "A-2"}})
	fmt.Println("3. a value outside the enum is refused by PostgreSQL, not by Go:\n   ", err)

	_, _ = q.Place(ctx, PlaceParams{Status: OrderStatusNew, Meta: types.Meta{SKU: "B-1"}})
	bySku, err := q.BySKU(ctx, "A-1")
	must(err)
	fmt.Println("4. filter on meta->>'sku' = 'A-1' found", len(bySku), "order, gift flag", bySku[0].Meta.Gift)

	sh, err := q.Ship(ctx, o.ID)
	must(err)
	fmt.Printf("5. Ship: status %s, prior %+v\n", sh.Status, sh.Prior)

	_, err = q.ByStatus(ctx, []OrderStatus{OrderStatusNew, OrderStatusShipped})
	fmt.Println("6. an array of the generated enum type:\n   ", err)
	rows, err := q.ByStatusText(ctx, []string{"new", "shipped"})
	must(err)
	fmt.Printf("   the same filter with status::text = any(text[]): %+v\n", rows)
	fmt.Println("7. the generated type is a plain string, so Go accepts any value:", OrderStatus("refunded"))
}

set -e
export PGOPTIONS='-c client_min_messages=warning'
DB=sqlc0695
docker exec -e PGOPTIONS="$PGOPTIONS" gh-lab-pg psql -U postgres -qc "drop database if exists $DB" -c "create database $DB"
docker exec -i -e PGOPTIONS="$PGOPTIONS" gh-lab-pg psql -U postgres -d $DB -q < schema.sql
"$HOME/go/bin/sqlc" generate
go run .

1. generated enum constants: new paid shipped cancelled
2. Order fields: Status main.OrderStatus, Prior main.NullOrderStatus, Meta types.Meta, Raw []uint8
   meta read back as a struct: {SKU:A-1 Gift:true Notes:[wrap it]}
   raw jsonb comes back as bytes: {"anything": [1, 2, 3]}
   prior is NULL: true
3. a value outside the enum is refused by PostgreSQL, not by Go:
    ERROR: invalid input value for enum order_status: "refunded" (SQLSTATE 22P02)
4. filter on meta->>'sku' = 'A-1' found 1 order, gift flag true
5. Ship: status shipped, prior {OrderStatus:paid Valid:true}
6. an array of the generated enum type:
    failed to encode args[0]: unable to encode []main.OrderStatus{"new", "shipped"} into text format for unknown type (OID 50392): cannot find encode plan
   the same filter with status::text = any(text[]): [{ID:1 Status:shipped} {ID:2 Status:new}]
7. the generated type is a plain string, so Go accepts any value: refunded

Typed jsonb. Place inserted a types.Meta and the returned Order held Meta types.Meta. It read back as {SKU:A-1 Gift:true Notes:[wrap it]}. The Raw field came back as []uint8 holding {"anything": [1, 2, 3]}, with PostgreSQL's own spacing, because jsonb normalises the text.

One consequence of typing a jsonb column with a struct: the struct is the schema. A row written by an older version, a script, or a different service that lacks a field decodes with the zero value and no error, and an extra field is dropped silently on decode. If the database must hold rows that Go does not understand, keep raw style bytes, or add json.Decoder.DisallowUnknownFields in a custom type.

Filtering inside the JSON. BySKU used meta->>'sku' = @sku::text, which found the single order with that SKU. The operator returns text, and the cast on the named parameter makes sqlc type it as a string. For queries that run often, an expression index on (meta->>'sku') or a gin index on the column is what makes them fast, which the full-text search article discusses for related cases.

Enums

An enum becomes a type and constants. sqlc generated OrderStatus, a string type, and four constants: OrderStatusNew, OrderStatusPaid, OrderStatusShipped and OrderStatusCancelled, which printed as new paid shipped cancelled. The nullable prior column became NullOrderStatus, a struct with OrderStatus and Valid, and after Ship it printed {OrderStatus:paid Valid:true}. For a new order prior was invalid, the way a NULL looks.

The type does not protect you in Go. The program inserted OrderStatus("refunded"), a conversion Go happily allows, and PostgreSQL rejected it: invalid input value for enum order_status: "refunded" (SQLSTATE 22P02). The error is the database's, at run time. So the type documents the allowed values and stops typos in code that uses the constants, but a value from outside (a request body decoded into a string) can still be converted into the type. Validate at the edge, and let the database be the backstop.

Adding a value to the enum is alter type order_status add value 'refunded', which is a migration like any other, and sqlc adds the constant on the next generate. Removing or renaming a value is harder in PostgreSQL (there is no drop value), which is why some teams use a lookup table or a text column with a check constraint for sets of values that change. A check constraint and a text column cost a little type safety on the Go side and make changes cheap.

The enum array that does not work

ByStatus takes @statuses::order_status[]. sqlc generated a method that takes []OrderStatus, and calling it failed:

failed to encode args[0]: unable to encode []main.OrderStatus{"new", "shipped"} into text format for unknown type (OID 50392): cannot find encode plan

pgx does not know the array type of a user defined enum. It knows text[], but the OID of order_status[] is not in its type map until you register it, and sqlc does not do that for you. The enum itself worked as a scalar in the same program because pgx falls back to text for an unknown scalar type with a string kind. There is no equivalent fallback for the array.

Two fixes. The one the lab used is to compare as text:

-- name: ByStatusText :many
select id, status from orders where status::text = any(@statuses::text[]) order by id;

The method takes a []string, and the lab got [{ID:1 Status:shipped} {ID:2 Status:new}]. The cost is that the comparison casts the column, so an index on status is not used for it; if that matters, create the index on (status::text). The other fix is to register the array type on each connection with pgx's LoadType in an AfterConnect hook, as the custom types article shows, which keeps the typed signature.

PostgreSQL arrays in general

Arrays of built in types work without ceremony. A text[] column is []string, an int[] is []int32 and bigint[] is []int64. A nullable array is the same slice, with nil for NULL, which is a little lossy: an empty slice and NULL are different values in PostgreSQL, and a nil slice sent as a parameter is NULL while []string{} is the empty array. Decide which you mean when a column is nullable.

What to take from it

The mapping rules are in sqlc's enum and array notes, and PostgreSQL's own pages on enumerated types and JSON functions and operators cover the database side.