PostgreSQL

PostgreSQL SQL uses double-quoted identifiers, ~ for regex, uppercase and/or, and JSONB operators for nested access.

Basic Examples

FlyQL	Generated SQL
message = 'hello'	"message" = 'hello'
message != 'hello'	"message" != 'hello'
count > 10	"count" > 10
count <= 100	"count" <= 100
message ~ 'error.*'	"message" ~ 'error.*'
message !~ 'test.*'	"message" !~ 'test.*'
host = 'prod'	"host" = 'prod'

Boolean Operators

PostgreSQL emits uppercase AND/OR:

FlyQL	Generated SQL
a = '1' and b > 10	"a" = '1' AND "b" > 10
a = '1' or b = '2'	"a" = '1' OR "b" = '2'
not a = '1'	NOT ("a" = '1')

List Membership

FlyQL	Generated SQL
status in [200, 201]	"status" IN (200, 201)
env not in ['dev', 'test']	"env" NOT IN ('dev', 'test')
status in []	FALSE
status not in []	TRUE

Truthy Checks

A bare column reference (active, not active) generates type-aware SQL — there is no single “is truthy” expression, because what counts as falsy differs by column type.

Column Type	FlyQL	Generated SQL
String	message	("message" IS NOT NULL AND "message" != '')
Int / Float	count	("count" IS NOT NULL AND "count" != 0)
Bool	active	"active"
Date	created_at	("created_at" IS NOT NULL)
Negated String	not message	("message" IS NULL OR "message" = '')

Containment (has / not has)

The has operator generates different SQL depending on the column type:

Column Type	FlyQL	Generated SQL
String	message has 'error'	position('error' in "message") > 0
String (negated)	message not has 'error'	("message" IS NULL OR position('error' in "message") = 0)
Array	tags has 'web'	'web' = ANY("tags")
Array (negated)	tags not has 'web'	NOT ('web' = ANY("tags"))
JSONB	data has 'key'	"data" ? 'key'
JSONB (negated)	data not has 'key'	NOT ("data" ? 'key')
Hstore	metadata has 'key'	"metadata" ? 'key'

JSONB Columns

PostgreSQL uses JSONB operators with type checking via jsonb_typeof(). The two-branch wrap is leaf-helper composite output — it survives the top-level WHERE-tree simplification.

String Comparison

FlyQL	Generated SQL
data.name = 'test'	(jsonb_typeof("data"->'name') = 'string' AND "data"->>'name' = 'test')
data.user.name = 'john'	(jsonb_typeof("data"->'user'->'name') = 'string' AND "data"->'user'->>'name' = 'john')

Numeric Comparison

Numeric comparisons cast the extracted value:

FlyQL	Generated SQL
data.age > 25	(jsonb_typeof("data"->'age') = 'number' AND ("data"->>'age')::numeric > 25)

JSONB Operators

• -> returns JSONB (used for intermediate path traversal)
• ->> returns text (used for the final value extraction)
• jsonb_typeof() checks the JSON type before comparison

Hstore (Map) Columns

PostgreSQL uses hstore type with the -> operator for key access:

FlyQL	Generated SQL
metadata.key1 = 'value1'	"metadata"->'key1' = 'value1'
metadata.key1 != 'value1'	"metadata"->'key1' != 'value1'
metadata.pattern ~ 'test.*'	"metadata"->'pattern' ~ 'test.*'

Array Columns

PostgreSQL arrays are 1-based — FlyQL automatically converts the 0-based index:

FlyQL	Generated SQL
tags.0 = 'first'	"tags"[1] = 'first'
tags.1 = 'second'	"tags"[2] = 'second'
tags.0 ~ 'tag.*'	"tags"[1] ~ 'tag.*'

Column Setup

Native jsonb/json columns are detected from the raw DB type. Text/varchar columns whose contents are valid JSON are declared with the synthetic jsonstring raw type:

// Go
columns := map[string]*postgresql.Column{
    "message": postgresql.NewColumn(postgresql.ColumnDef{Name: "message", Type: "varchar"}),
    "data":    postgresql.NewColumn(postgresql.ColumnDef{Name: "data", Type: "jsonb"}),
    "log":     postgresql.NewColumn(postgresql.ColumnDef{Name: "log", Type: "jsonstring"}),
}

// JavaScript
const columns = {
    message: newColumn({ name: "message", type: "varchar" }),
    data:    newColumn({ name: "data", type: "jsonb" }),
    log:     newColumn({ name: "log", type: "jsonstring" }),
}

Transformers

Transformers generate PostgreSQL-standard function calls with double-quoted identifiers inside the transformer:

FlyQL	Generated SQL
status|upper = "ERROR"	UPPER("status") = 'ERROR'
status|lower = "info"	LOWER("status") = 'info'
message|len > 100	LENGTH("message") > 100
message|lower|len > 100	LENGTH(LOWER("message")) > 100
tags|split(",") has "urgent"	'urgent' = ANY(STRING_TO_ARRAY("tags", ','))