Operators Reference

Complete list of operators available in ASTQL.

Comparison Operators

Basic comparison operators for WHERE conditions.

Constant	SQL	Description	Example
`EQ`	`=`	Equals	`"id" = :id`
`NE`	`!=`	Not equals	`"status" != :status`
`GT`	`>`	Greater than	`"price" > :min`
`GE`	`>=`	Greater than or equal	`"age" >= :min_age`
`LT`	`<`	Less than	`"date" < :cutoff`
`LE`	`<=`	Less than or equal	`"count" <= :max`

Usage

instance.C(instance.F("id"), astql.EQ, instance.P("id"))
// "id" = :id

Pattern Matching

String pattern matching operators.

Constant	SQL	Description	Example
`LIKE`	`LIKE`	Pattern match (case-sensitive)	`"name" LIKE :pattern`
`NotLike`	`NOT LIKE`	Pattern non-match	`"name" NOT LIKE :pattern`
`ILIKE`	`ILIKE`	Pattern match (case-insensitive)	`"name" ILIKE :pattern`
`NotILike`	`NOT ILIKE`	Pattern non-match (case-insensitive)	`"name" NOT ILIKE :pattern`

Usage

instance.C(instance.F("email"), astql.ILIKE, instance.P("search"))
// "email" ILIKE :search

// With wildcards in parameter value
params := map[string]any{"search": "%@example.com"}

NULL Operators

Check for NULL values.

Constant	SQL	Description	Example
`IsNull`	`IS NULL`	Value is NULL	`"deleted_at" IS NULL`
`IsNotNull`	`IS NOT NULL`	Value is not NULL	`"verified_at" IS NOT NULL`

Usage

instance.Null(instance.F("deleted_at"))
// "deleted_at" IS NULL

instance.NotNull(instance.F("email"))
// "email" IS NOT NULL

Array Operators

PostgreSQL array membership operators.

Constant	SQL	Description	Example
`IN`	`= ANY()`	Value in array	`"id" = ANY(:ids)`
`NotIn`	`!= ALL()`	Value not in array	`"status" != ALL(:excluded)`

Usage

instance.C(instance.F("id"), astql.IN, instance.P("ids"))
// "id" = ANY(:ids)

instance.C(instance.F("status"), astql.NotIn, instance.P("excluded"))
// "status" != ALL(:excluded)

With sqlx

import "github.com/lib/pq"

params := map[string]any{
    "ids": pq.Array([]int{1, 2, 3}),
    "excluded": pq.Array([]string{"draft", "deleted"}),
}

Subquery Operators

Operators for subquery conditions.

Constant	SQL	Description
`IN`	`IN`	Value in subquery results
`NotIn`	`NOT IN`	Value not in subquery results
`EXISTS`	`EXISTS`	Subquery returns rows
`NotExists`	`NOT EXISTS`	Subquery returns no rows

Usage

// IN subquery
subquery := astql.Sub(astql.Select(table).Fields(field))
astql.CSub(instance.F("id"), astql.IN, subquery)
// "id" IN (SELECT "user_id" FROM "orders")

// EXISTS
astql.CSubExists(astql.EXISTS, subquery)
// EXISTS (SELECT "id" FROM "orders" WHERE ...)

Regex Operators

PostgreSQL regular expression operators.

Constant	SQL	Description	Example
`RegexMatch`	`~`	Regex match (case-sensitive)	`"code" ~ :pattern`
`RegexIMatch`	`~*`	Regex match (case-insensitive)	`"name" ~* :pattern`
`NotRegexMatch`	`!~`	Regex non-match	`"email" !~ :pattern`
`NotRegexIMatch`	`!~*`	Regex non-match (case-insensitive)	`"name" !~* :pattern`

Usage

instance.C(instance.F("code"), astql.RegexMatch, instance.P("pattern"))
// "code" ~ :pattern

params := map[string]any{"pattern": "^[A-Z]{3}-[0-9]+$"}

PostgreSQL Array Operators

Array containment and overlap operators.

Constant	SQL	Description	Example
`ArrayContains`	`@>`	Array contains	`"tags" @> :required_tags`
`ArrayContainedBy`	`<@`	Array is contained by	`"permissions" <@ :allowed`
`ArrayOverlap`	`&&`	Arrays overlap	`"categories" && :search_cats`

Usage

instance.C(instance.F("tags"), astql.ArrayContains, instance.P("required"))
// "tags" @> :required

params := map[string]any{
    "required": pq.Array([]string{"featured", "published"}),
}

JSONB Field Access (PostgreSQL)

Access JSONB object keys directly in queries. Keys are parameterized for SQL injection safety.

Method	SQL	Description	Example
`JSONBText()`	`->>`	Extract as text	`"metadata"->>:key_param`
`JSONBPath()`	`->`	Extract as JSON	`"metadata"->:key_param`

Usage

// Text extraction (returns string) - key is parameterized
statusField := instance.JSONBText(instance.F("metadata"), instance.P("status_key"))
// "metadata"->>:status_key

// Path access (returns JSONB, use with array operators)
tagsField := instance.JSONBPath(instance.F("metadata"), instance.P("tags_key"))
// "metadata"->:tags_key

// In WHERE clause
instance.C(statusField, astql.EQ, instance.P("status_value"))
// "metadata"->>:status_key = :status_value

// JSONB array containment
instance.C(tagsField, astql.ArrayContains, instance.P("required_tags"))
// "metadata"->:tags_key @> :required_tags

Both the JSONB key and the comparison value are parameterized, preventing SQL injection.

Vector Operators (pgvector)

Distance operators for vector similarity search.

Constant	SQL	Description	Use Case
`VectorL2Distance`	`<->`	Euclidean (L2) distance	General similarity
`VectorCosineDistance`	`<=>`	Cosine distance	Text embeddings
`VectorInnerProduct`	`<#>`	Negative inner product	Maximum similarity
`VectorL1Distance`	`<+>`	Manhattan (L1) distance	Sparse vectors

Usage

// In WHERE clause
instance.C(instance.F("embedding"), astql.VectorL2Distance, instance.P("query"))
// "embedding" <-> :query

// In ORDER BY (most common)
query.OrderByExpr(
    instance.F("embedding"),
    astql.VectorCosineDistance,
    instance.P("query_embedding"),
    astql.ASC,
)
// ORDER BY "embedding" <=> :query_embedding ASC

With pgvector

import "github.com/pgvector/pgvector-go"

embedding := pgvector.NewVector([]float32{0.1, 0.2, 0.3, ...})
params := map[string]any{"query_embedding": embedding}

Operator Selection Guide

Need	Operator
Exact match	`EQ`
Not equal	`NE`
Range check	`GT`, `GE`, `LT`, `LE`
Pattern search	`LIKE`, `ILIKE`
NULL check	`IsNull`, `IsNotNull`
List membership	`IN`, `NotIn`
Regex match	`RegexMatch`, `RegexIMatch`
Array containment	`ArrayContains`, `ArrayContainedBy`
Vector similarity	`VectorL2Distance`, `VectorCosineDistance`
Subquery check	`EXISTS`, `NotExists`

Aggregate Functions

For use with SelectExpr and aggregate expressions.

Constant	SQL	Description
`AggSum`	`SUM()`	Sum of values
`AggAvg`	`AVG()`	Average of values
`AggMin`	`MIN()`	Minimum value
`AggMax`	`MAX()`	Maximum value
`AggCountField`	`COUNT()`	Count of values
`AggCountDistinct`	`COUNT(DISTINCT)`	Count of unique values

Window Functions

For use with window expressions.

Constant	SQL	Description
`WinRowNumber`	`ROW_NUMBER()`	Sequential row number
`WinRank`	`RANK()`	Rank with gaps
`WinDenseRank`	`DENSE_RANK()`	Rank without gaps
`WinNtile`	`NTILE(:param)`	Divide into n buckets
`WinLag`	`LAG()`	Previous row value
`WinLead`	`LEAD()`	Next row value
`WinFirstValue`	`FIRST_VALUE()`	First value in window
`WinLastValue`	`LAST_VALUE()`	Last value in window

Frame Bounds

For window frame specifications.

Constant	SQL
`FrameUnboundedPreceding`	`UNBOUNDED PRECEDING`
`FrameCurrentRow`	`CURRENT ROW`
`FrameUnboundedFollowing`	`UNBOUNDED FOLLOWING`

Cast Types

For type casting with Cast().

Constant	PostgreSQL Type
`CastText`	`TEXT`
`CastInteger`	`INTEGER`
`CastBigint`	`BIGINT`
`CastSmallint`	`SMALLINT`
`CastNumeric`	`NUMERIC`
`CastReal`	`REAL`
`CastDoublePrecision`	`DOUBLE PRECISION`
`CastBoolean`	`BOOLEAN`
`CastDate`	`DATE`
`CastTime`	`TIME`
`CastTimestamp`	`TIMESTAMP`
`CastTimestampTZ`	`TIMESTAMPTZ`
`CastInterval`	`INTERVAL`
`CastUUID`	`UUID`
`CastJSON`	`JSON`
`CastJSONB`	`JSONB`
`CastBytea`	`BYTEA`