@terascope/sql-builder / dialects/postgres / PostgresDialect

Class: PostgresDialect

Defined in: dialects/postgres.ts:37

The PostgreSQL dialect, using PostGIS for geo queries.

There is no toDuckDB equivalent for PostgreSQL, so this dialect assumes these column types:

xLucene type	column
geo-point, geo	a PostGIS geometry(Point, 4326)
geo-json	text or json/jsonb, cast through ST_GeomFromGeoJSON
ip, ip_range	text, cast to inet
date	timestamp holding UTC
a dotted field	one column whose name contains a dot

Storage that differs can be supported by subclassing and overriding the affected methods.

The generated SQL is tested as text only; it is not run against a PostgreSQL server in this repo's tests. The IP helpers come from BaseSQLDialect, because PostgreSQL orders inet by family first just as DuckDB does.

Extends

• BaseSQLDialect

Constructors

Constructor

new PostgresDialect(): PostgresDialect

Returns

PostgresDialect

Inherited from

BaseSQLDialect.constructor

Properties

Property	Modifier	Type	Default value	Overrides	Defined in
name	readonly	postgres	SQLDialectName.postgres	BaseSQLDialect.name	dialects/postgres.ts:38

Methods

and()

and(expressions): string

Defined in: dialects/base.ts:189

All of the expressions.

Parameters

Parameter	Type
expressions	string[]

Returns

string

Inherited from

BaseSQLDialect.and

---

blockEnd()

protected blockEnd(fieldExpr): string

Defined in: dialects/base.ts:340

Parameters

Parameter	Type
fieldExpr	string

Returns

string

Inherited from

BaseSQLDialect.blockEnd

---

blockStart()

protected blockStart(fieldExpr): string

Defined in: dialects/base.ts:336

The first and last addresses of the block an ip_range column holds.

host() strips the mask. Comparing a masked INET with a bare one also compares the masks, which gives the wrong answer for overlapping blocks.

Parameters

Parameter	Type
fieldExpr	string

Returns

string

Inherited from

BaseSQLDialect.blockStart

---

compare()

compare(fieldExpr, operator, value, fieldType?): string

Defined in: dialects/base.ts:144

One side of a range.

Parameters

Parameter	Type
fieldExpr	string
operator	"gt" | "gte" | "lt" | "lte"
value	unknown
fieldType?	xLuceneFieldType

Returns

string

Inherited from

BaseSQLDialect.compare

---

equals()

equals(fieldExpr, value, fieldType?): string

Defined in: dialects/base.ts:128

Equality against a single value.

Parameters

Parameter	Type
fieldExpr	string
value	unknown
fieldType?	xLuceneFieldType

Returns

string

Inherited from

BaseSQLDialect.equals

---

exists()

exists(fieldExpr): string

Defined in: dialects/base.ts:174

The field has a value.

Parameters

Parameter	Type
fieldExpr	string

Returns

string

Inherited from

BaseSQLDialect.exists

---

fieldRef()

fieldRef(field): string

Defined in: dialects/postgres.ts:46

A dotted xLucene field is one column name.

Unquoted, foo.bar would mean column bar of table foo. Quoting in PostgreSQL is also case-sensitive, so this matches a column created with that exact spelling.

Parameters

Parameter	Type
field	string

Returns

string

Overrides

BaseSQLDialect.fieldRef

---

geoContainsPoint()

geoContainsPoint(fieldExpr, point, isPointColumn): string

Defined in: dialects/postgres.ts:105

Boundary-inclusive, matching Elasticsearch's geo_shape intersects relation.

Parameters

Parameter	Type
fieldExpr	string
point	GeoPoint
isPointColumn	boolean

Returns

string

Overrides

BaseSQLDialect.geoContainsPoint

---

geoPointDistance()

geoPointDistance(fieldExpr, point): string

Defined in: dialects/postgres.ts:62

geography rather than geometry, so the distance comes back in meters.

Parameters

Parameter	Type
fieldExpr	string
point	GeoPoint

Returns

string

Overrides

BaseSQLDialect.geoPointDistance

---

geoPointEquals()

protected geoPointEquals(fieldExpr, point): string

Defined in: dialects/base.ts:287

A geo-point column equal to one point, which is what a bare term on it means.

Parameters

Parameter	Type
fieldExpr	string
point	GeoPoint

Returns

string

Inherited from

BaseSQLDialect.geoPointEquals

---

geoPointInBoundingBox()

geoPointInBoundingBox(fieldExpr, topLeft, bottomRight): string

Defined in: dialects/postgres.ts:77

Arithmetic, for the same boundary-inclusiveness reason as the DuckDB dialect.

Parameters

Parameter	Type
fieldExpr	string
topLeft	GeoPoint
bottomRight	GeoPoint

Returns

string

Overrides

BaseSQLDialect.geoPointInBoundingBox

---

geoPointParts()

geoPointParts(fieldExpr): SQLGeoPointColumn

Defined in: dialects/postgres.ts:54

The lat and lon members of a geo-point column.

Parameters

Parameter	Type
fieldExpr	string

Returns

SQLGeoPointColumn

Overrides

BaseSQLDialect.geoPointParts

---

geoPointWithinDistance()

geoPointWithinDistance(fieldExpr, point, meters): string

Defined in: dialects/postgres.ts:70

ST_DWithin rather than a comparison against ST_Distance, because it is the form PostGIS can answer from a spatial index.

Parameters

Parameter	Type
fieldExpr	string
point	GeoPoint
meters	number

Returns

string

Overrides

BaseSQLDialect.geoPointWithinDistance

---

geoRelation()

geoRelation(fieldExpr, shape, relation, isPointColumn): string

Defined in: dialects/postgres.ts:87

A geo field related to a shape.

isPointColumn says whether the field holds a point or a shape, which decides how the column becomes a geometry.

Parameters

Parameter	Type
fieldExpr	string
shape	GeoShape
relation	GeoShapeRelation
isPointColumn	boolean

Returns

string

Overrides

BaseSQLDialect.geoRelation

---

inetLiteral()

protected inetLiteral(value): string

Defined in: dialects/postgres.ts:119

A literal IP address or CIDR block.

Parameters

Parameter	Type
value	string

Returns

string

Overrides

BaseSQLDialect.inetLiteral

---

ipComparison()

protected ipComparison(fieldExpr, operator, value): string

Defined in: dialects/base.ts:293

One side of a range on an ip field, compared as an address.

Parameters

Parameter	Type
fieldExpr	string
operator	string
value	string

Returns

string

Inherited from

BaseSQLDialect.ipComparison

---

ipEquals()

ipEquals(fieldExpr, address): string

Defined in: dialects/base.ts:205

Equality over the mapped form, so ::ffff:8.8.8.8 and 8.8.8.8 are one address.

Parameters

Parameter	Type
fieldExpr	string
address	string

Returns

string

Inherited from

BaseSQLDialect.ipEquals

---

ipInCIDR()

ipInCIDR(fieldExpr, cidr): string

Defined in: dialects/base.ts:198

CIDR containment, comparing both sides in IPv4-mapped IPv6 form (see mappedInet).

Parameters

Parameter	Type
fieldExpr	string
cidr	string

Returns

string

Inherited from

BaseSQLDialect.ipInCIDR

---

ipRangeContains()

ipRangeContains(fieldExpr, value): string

Defined in: dialects/base.ts:220

An ip_range column containing a queried address or block.

Here the column holds the block and the query holds the address, the reverse of ipInCIDR. It is written as an overlap check against the block's endpoints rather than with <<=, because <<= returns false across address families ('::ffff:10.0.0.1'::INET <<= '10.0.0.0/30'::INET is FALSE), while the mapped endpoints compare the way Elasticsearch does.

Parameters

Parameter	Type
fieldExpr	string
value	string

Returns

string

Inherited from

BaseSQLDialect.ipRangeContains

---

ipRangeIntersects()

ipRangeIntersects(fieldExpr, start?, end?): string

Defined in: dialects/base.ts:240

Two blocks overlap when each one starts at or before the other one ends.

An exclusive bound changes the operator, not the value: {a TO b} overlaps a stored block only if the block starts before b and ends after a, so a block that only touches a bound does not match.

Parameters

Parameter	Type
fieldExpr	string
start?	SQLIPRangeBound
end?	SQLIPRangeBound

Returns

string

Inherited from

BaseSQLDialect.ipRangeIntersects

---

join()

protected join(expressions, operator): string

Defined in: dialects/base.ts:361

Parameters

Parameter	Type
expressions	string[]
operator	"AND" | "OR"

Returns

string

Inherited from

BaseSQLDialect.join

---

limitOffset()

limitOffset(size?, offset?): string

Defined in: dialects/base.ts:101

LIMIT/OFFSET, which DuckDB and PostgreSQL write the same way.

Both numbers come from the request and are written into the statement, so they are validated as non-negative integers to prevent SQL injection.

Parameters

Parameter	Type
size?	number
offset?	number

Returns

string

Inherited from

BaseSQLDialect.limitOffset

---

literal()

literal(value, fieldType?): string

Defined in: dialects/base.ts:64

A value rendered as a literal of the given xLucene field type.

Parameters

Parameter	Type
value	unknown
fieldType?	xLuceneFieldType

Returns

string

Inherited from

BaseSQLDialect.literal

---

mappedInet()

protected mappedInet(fieldExpr): string

Defined in: dialects/base.ts:306

The column as an address that compares correctly with any other address.

Both engines order INET by family first, so every IPv4 address sorts before every IPv6 one. Elasticsearch stores every ip as 128 bits with IPv4 mapped into IPv6 and orders numerically. Mapping IPv4 values into ::ffff: form puts everything in one family, so ranges compare numerically and ::ffff:8.8.8.8 matches a stored 8.8.8.8, as in Elasticsearch.

Parameters

Parameter	Type
fieldExpr	string

Returns

string

Inherited from

BaseSQLDialect.mappedInet

---

mappedInetLiteral()

protected mappedInetLiteral(value): string

Defined in: dialects/base.ts:319

The same mapping for a literal.

A CIDR prefix length grows by 96 bits with the mapping, so 8.8.8.0/24 becomes ::ffff:8.8.8.0/120.

Parameters

Parameter	Type
value	string

Returns

string

Inherited from

BaseSQLDialect.mappedInetLiteral

---

matchAll()

matchAll(): string

Defined in: dialects/base.ts:120

TRUE - an empty query matches everything.

Returns

string

Inherited from

BaseSQLDialect.matchAll

---

matchNone()

matchNone(): string

Defined in: dialects/base.ts:124

FALSE - a query that cannot match anything.

Returns

string

Inherited from

BaseSQLDialect.matchNone

---

not()

not(expression): string

Defined in: dialects/base.ts:185

Negate an expression so a missing field matches, as in Elasticsearch.

Elasticsearch's must_not matches a document whose field is absent. In SQL, NOT (col = 'x') is NULL when col is NULL, and WHERE drops that row, so the COALESCE turns the NULL into TRUE.

Parameters

Parameter	Type
expression	string

Returns

string

Inherited from

BaseSQLDialect.not

---

or()

or(expressions): string

Defined in: dialects/base.ts:193

Any of the expressions.

Parameters

Parameter	Type
expressions	string[]

Returns

string

Inherited from

BaseSQLDialect.or

---

orderBy()

orderBy(sort?): string

Defined in: dialects/base.ts:116

The ORDER BY terms, with direction and null placement both written out.

NULLS FIRST / NULLS LAST is standard SQL, so this is shared. It is always written because no engine's default matches DataFrame's null order (see defaultNullOrder).

Parameters

Parameter	Type
sort?	readonly SQLSort[]

Returns

string

Inherited from

BaseSQLDialect.orderBy

---

projection()

projection(readable, _all): string

Defined in: dialects/base.ts:89

One column per readable path.

This is correct when a dotted field is a single flat column. An engine that stores dotted fields as nested structs overrides it, so a partly readable parent is rebuilt rather than dropped.

Parameters

Parameter	Type
readable	readonly string[]
_all	readonly string[]

Returns

string

Inherited from

BaseSQLDialect.projection

---

quoteIdentifier()

quoteIdentifier(name): string

Defined in: dialects/base.ts:54

Quote an identifier for use as a column reference.

Parameters

Parameter	Type
name	string

Returns

string

Inherited from

BaseSQLDialect.quoteIdentifier

---

regexp()

regexp(fieldExpr, value, fieldType?): string

Defined in: dialects/base.ts:169

An anchored regular expression match, as the Elasticsearch regexp query is.

Parameters

Parameter	Type
fieldExpr	string
value	string
fieldType?	xLuceneFieldType

Returns

string

Inherited from

BaseSQLDialect.regexp

---

regexpMatch()

protected regexpMatch(expr, value): string

Defined in: dialects/postgres.ts:133

~ searches rather than matching the whole string, so the pattern is anchored to match Elasticsearch's regexp query. The non-capturing group keeps an alternation inside the anchors: ^a|b$ would mean ^a or b$.

Parameters

Parameter	Type
expr	string
value	string

Returns

string

Overrides

BaseSQLDialect.regexpMatch

---

stringLiteral()

stringLiteral(value): string

Defined in: dialects/base.ts:60

A SQL string literal, escaped for this engine.

Parameters

Parameter	Type
value	string

Returns

string

Inherited from

BaseSQLDialect.stringLiteral

---

textContains()

protected textContains(expr, substring): string

Defined in: dialects/postgres.ts:124

PostgreSQL has no contains, so this uses strpos.

Parameters

Parameter	Type
expr	string
substring	string

Returns

string

Overrides

BaseSQLDialect.textContains

---

timestampLiteral()

protected timestampLiteral(value): string

Defined in: dialects/base.ts:357

A literal timestamp, always UTC.

Parameters

Parameter	Type
value	unknown

Returns

string

Inherited from

BaseSQLDialect.timestampLiteral

---

toInet()

protected toInet(fieldExpr): string

Defined in: dialects/postgres.ts:115

PostgreSQL has no TRY_CAST, so a row whose text is not an address fails the whole query. Use an inet column or a CHECK constraint if the column may hold invalid values.

Parameters

Parameter	Type
fieldExpr	string

Returns

string

Overrides

BaseSQLDialect.toInet

---

toText()

toText(fieldExpr): string

Defined in: dialects/postgres.ts:50

The value cast to text, for a query with no field.

Parameters

Parameter	Type
fieldExpr	string

Returns

string

Overrides

BaseSQLDialect.toText

---

validCIDR()

protected validCIDR(value): string

Defined in: dialects/base.ts:383

Parameters

Parameter	Type
value	unknown

Returns

string

Inherited from

BaseSQLDialect.validCIDR

---

validIP()

protected validIP(value): string

Defined in: dialects/base.ts:372

Validate an IP address or CIDR block before it reaches the engine.

Otherwise the engine's parse error would refer to generated SQL the caller never wrote.

Parameters

Parameter	Type
value	unknown

Returns

string

Inherited from

BaseSQLDialect.validIP

---

wildcard()

wildcard(fieldExpr, value, fieldType?): string

Defined in: dialects/base.ts:159

A wildcard match, given an xLucene wildcard value (* and ?).

Parameters

Parameter	Type
fieldExpr	string
value	string
fieldType?	xLuceneFieldType

Returns

string

Inherited from

BaseSQLDialect.wildcard