Go type mappings

Annotations use Postgres type names, not Go type names. A ? after the type means the value can be NULL. Here, text? produces *string in Go:

-- plainsql: query GetPostBody returns one
-- plainsql: column body text?
SELECT body FROM posts WHERE id = $1;

body, err := queries.GetPostBody(ctx, 1) // *string, error

A nullable value becomes a pointer in Go. Arrays and bytea are the exception. They are already slices, and a nil slice represents NULL.

Built-in mappings

Postgres	Go
boolean	bool
smallint, integer, bigint	int16, int32, int64
real, double precision	float32, float64
text, varchar, bpchar, name	string
bytea	[]byte
uuid	uuid.UUID from the Go 1.27 standard library
date, timestamp, timestamptz	time.Time
time, interval, numeric	pgtype.Time, pgtype.Interval, pgtype.Numeric
json, jsonb	json.RawMessage
inet, cidr	netip.Addr, netip.Prefix
macaddr, macaddr8	net.HardwareAddr
oid, cid, xid, xid8	uint32, uint32, uint32, uint64
bit, varbit	pgtype.Bits
box, circle, line, lseg, path, point, polygon, tid	Corresponding pgtype type
aclitem, jsonpath, xml	string
tsvector	pgtype.TSVector
Postgres internal "char"	byte

A Postgres enum becomes a named string type with one constant per value, a Valid method, and an All<Type>Values function. A domain type uses the mapping of its base type unless you override the domain itself.

bigint[] and bigint[]? both map to []int64:

[]int64(nil)  // SQL NULL
[]int64{}     // Empty SQL array
[]int64{1, 2} // Two elements

[]int64 can hold {1,2,3} but not {1,NULL,3}, because an int64 element cannot be NULL. For arrays that contain NULL elements, override the type with pgtype.Array[pgtype.Int8].

Also use pgtype.Array[T] for nested arrays such as {{1,2},{3,4}}, or for arrays whose first index is not 1.

Override a database type

You can map a Postgres type to your own Go type. First, define the type outside the generated directory, for example in internal/settings/preferences.go:

package settings

type Preferences struct {
    Theme              string `json:"theme"`
    EmailNotifications bool   `json:"email_notifications"`
}

Then map jsonb to it in plainsql.yaml:

generate:
  go:
    package: db
    dir: gen/db
    types:
      jsonb:
        import: example.com/blog/internal/settings
        type: Preferences

PlainSQL now uses settings.Preferences for every jsonb column and parameter, and *settings.Preferences when the value is nullable. pgx encodes and decodes the struct as JSON.