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" LIKE 'prod%'

Boolean Operators

PostgreSQL uses uppercase AND/OR (unlike ClickHouse and StarRocks):

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

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():

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

PostgreSQL columns do not have a jsonString parameter — JSONB type is detected from the column type:

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

// JavaScript
const columns = {
    message: newColumn("message", "varchar", null),
    data:    newColumn("data", "jsonb", null),
}