tessl/golang-github-com-jackc-pgx-v5

pgx is a pure Go driver and toolkit for PostgreSQL providing a native high-performance interface with PostgreSQL-specific features plus a database/sql compatibility adapter.

Overview

Eval results

Files

Ranges & Multiranges

Name: tessl/golang-github-com-jackc-pgx-v5
Author: tessl

PostgreSQL range and multirange type support.

Import

import "github.com/jackc/pgx/v5/pgtype"

Range Types

type Range[T any] struct {
    Lower     T
    Upper     T
    LowerType BoundType
    UpperType BoundType
    Valid     bool
}

func (r Range[T]) BoundTypes() (lower, upper BoundType)
func (r Range[T]) Bounds() (lower, upper any)
func (r Range[T]) IsNull() bool
func (r *Range[T]) ScanBounds() (lowerTarget, upperTarget any)
func (r *Range[T]) ScanNull() error
func (r *Range[T]) SetBoundTypes(lower, upper BoundType) error

type BoundType byte

const (
    Inclusive  BoundType = 'i'
    Exclusive  BoundType = 'e'
    Unbounded  BoundType = 'U'
    Empty      BoundType = 'E'
)

func (bt BoundType) String() string

Common Range Types

PostgreSQL provides built-in range types:

int4range — int32 range
int8range — int64 range
numrange — numeric range
tsrange — timestamp without time zone range
tstzrange — timestamp with time zone range
daterange — date range

Range Usage

Query Range

var dateRange pgtype.Range[pgtype.Date]
err := conn.QueryRow(ctx,
    "SELECT availability FROM schedules WHERE id = $1", id).Scan(&dateRange)

if dateRange.Valid {
    fmt.Printf("Lower: %v (%s), Upper: %v (%s)
",
        dateRange.Lower, dateRange.LowerType,
        dateRange.Upper, dateRange.UpperType)
}

Insert Range

dateRange := pgtype.Range[pgtype.Date]{
    Lower: pgtype.Date{Time: time.Date(2024, 1, 1, 0, 0, 0, 0, time.UTC), Valid: true},
    Upper: pgtype.Date{Time: time.Date(2024, 12, 31, 0, 0, 0, 0, time.UTC), Valid: true},
    LowerType: pgtype.Inclusive,
    UpperType: pgtype.Inclusive,
    Valid: true,
}

_, err := conn.Exec(ctx,
    "INSERT INTO schedules (availability) VALUES ($1)", dateRange)

Integer Range

var ageRange pgtype.Range[pgtype.Int4]
err := conn.QueryRow(ctx,
    "SELECT age_range FROM demographics WHERE id = $1", id).Scan(&ageRange)

if ageRange.Valid {
    if ageRange.LowerType != pgtype.Unbounded {
        fmt.Println("Min age:", ageRange.Lower.Int32)
    }
    if ageRange.UpperType != pgtype.Unbounded {
        fmt.Println("Max age:", ageRange.Upper.Int32)
    }
}

Timestamp Range

start := time.Now()
end := start.Add(24 * time.Hour)

tsRange := pgtype.Range[pgtype.Timestamptz]{
    Lower: pgtype.Timestamptz{Time: start, Valid: true},
    Upper: pgtype.Timestamptz{Time: end, Valid: true},
    LowerType: pgtype.Inclusive,
    UpperType: pgtype.Exclusive,
    Valid: true,
}

_, err := conn.Exec(ctx,
    "INSERT INTO reservations (time_slot) VALUES ($1)", tsRange)

RangeValuer and RangeScanner Interfaces

// RangeValuer is a type that can be converted into a PostgreSQL range.
type RangeValuer interface {
    IsNull() bool
    BoundTypes() (lower, upper BoundType)
    Bounds() (lower, upper any)
}

// RangeScanner is a type that can be scanned from a PostgreSQL range.
type RangeScanner interface {
    ScanNull() error
    ScanBounds() (lowerTarget, upperTarget any)
    SetBoundTypes(lower, upper BoundType) error
}

Implement these for custom range types.

Multirange Types

type Multirange[T RangeValuer] []T

func (r Multirange[T]) Index(i int) any
func (r Multirange[T]) IndexType() any
func (r Multirange[T]) IsNull() bool
func (r Multirange[T]) Len() int
func (r Multirange[T]) ScanIndex(i int) any
func (r Multirange[T]) ScanIndexType() any
func (r *Multirange[T]) ScanNull() error
func (r *Multirange[T]) SetLen(n int) error

Multiranges represent discontinuous ranges (PostgreSQL 14+).

Multirange Usage

Query Multirange

var availability pgtype.Multirange[pgtype.Range[pgtype.Timestamptz]]
err := conn.QueryRow(ctx,
    "SELECT available_slots FROM schedules WHERE id = $1", id).Scan(&availability)

for i, slot := range availability {
    fmt.Printf("Slot %d: %v to %v
", i+1, slot.Lower.Time, slot.Upper.Time)
}

Insert Multirange

slots := pgtype.Multirange[pgtype.Range[pgtype.Timestamptz]]{
    {
        Lower: pgtype.Timestamptz{Time: time.Date(2024, 1, 1, 9, 0, 0, 0, time.UTC), Valid: true},
        Upper: pgtype.Timestamptz{Time: time.Date(2024, 1, 1, 12, 0, 0, 0, time.UTC), Valid: true},
        LowerType: pgtype.Inclusive,
        UpperType: pgtype.Exclusive,
        Valid: true,
    },
    {
        Lower: pgtype.Timestamptz{Time: time.Date(2024, 1, 1, 14, 0, 0, 0, time.UTC), Valid: true},
        Upper: pgtype.Timestamptz{Time: time.Date(2024, 1, 1, 17, 0, 0, 0, time.UTC), Valid: true},
        LowerType: pgtype.Inclusive,
        UpperType: pgtype.Exclusive,
        Valid: true,
    },
}

_, err := conn.Exec(ctx,
    "INSERT INTO schedules (available_slots) VALUES ($1)", slots)

Multirange Interfaces

type MultirangeGetter interface {
    IsNull() bool
    Len() int
    Index(i int) any
    IndexType() any
}

type MultirangeSetter interface {
    ScanNull() error
    SetLen(n int) error
    ScanIndex(i int) any
    ScanIndexType() any
}

RangeCodec and MultirangeCodec

// RangeCodec — codec for any range type
type RangeCodec struct {
    ElementType *Type
}

// MultirangeCodec — codec for any multirange type
type MultirangeCodec struct {
    ElementType *Type
}

Used internally for custom range/multirange types.

Empty and Unbounded Ranges

// Empty range
emptyRange := pgtype.Range[pgtype.Int4]{
    LowerType: pgtype.Empty,
    UpperType: pgtype.Empty,
    Valid: true,
}

// Unbounded on both sides
unboundedRange := pgtype.Range[pgtype.Int8]{
    LowerType: pgtype.Unbounded,
    UpperType: pgtype.Unbounded,
    Valid: true,
}

// Unbounded lower, bounded upper
halfBoundedRange := pgtype.Range[pgtype.Int4]{
    Upper: pgtype.Int4{Int32: 100, Valid: true},
    LowerType: pgtype.Unbounded,
    UpperType: pgtype.Exclusive,
    Valid: true,
}

Install with Tessl CLI

npx tessl i tessl/golang-github-com-jackc-pgx-v5@5.8.0