Filters and ordering

Each Search2 column filter selects one public column, one operator, and usually one value. Range uses two values; null checks use none. All filters must match for a row to be returned.

Filter types

Filter	SQL meaning	Typical use
Equals	column = value	Exact status, code, number, or date
Contains	column ILIKE %value%	Case-insensitive text fragment
Starts with	column ILIKE value%	Text prefix
Ends with	column ILIKE %value	Text suffix
Range	column BETWEEN lower AND upper	Inclusive interval
Greater than	column > value	Strict lower boundary
Less than	column < value	Strict upper boundary
Is null	column IS NULL	Missing value
Is not null	column IS NOT NULL	Present value

Equals

Equals uses the database’s ordinary equality operator. Text equality is case-sensitive. It compares the complete value, with no trimming, accent folding, typo tolerance, or substring behavior.

Use Contains when a case-insensitive text fragment is intended. Use Equals for controlled values where Issued and issued are meaningfully different according to the stored data.

Contains, starts with, and ends with

These three filters use PostgreSQL ILIKE, making text matching case-insensitive. Search2 adds the surrounding wildcard needed by the selected operator.

Input % and _ characters remain active wildcards:

Input	Pattern meaning
A% in Contains	Contains A, followed by any sequence
_BC in Starts with	Any one character, followed by BC
% in Contains	Matches every non-null text value

Pattern filters are intended for text-compatible columns. Search2 does not cast the selected column to text for these filters.

Range

Range is inclusive at both ends. A range from 2026-01-01 to 2026-12-31 includes rows equal to either boundary.

Both values are required. Omitting the upper value rejects the request before SQL execution.

Greater than and less than

These comparisons are strict: Greater than excludes a value equal to its boundary, and Less than does the same.

Null checks

Is null and Is not null ignore the filter’s ordinary value fields. An empty string is not null, so use Equals with an empty value when that distinction matters.

Column types matter

Search2 builds direct SQL predicates and supplies filter inputs as strings. It does not parse each value according to the table definition before executing the query.

Consequently, PostgreSQL and the database driver decide whether an operator and value are compatible with the selected column. Pattern operators belong on text columns. Numeric, money, boolean, and temporal comparisons may fail when their SQL type cannot be compared with the supplied text parameter.

No schema-aware filter conversion

Do not assume that a value accepted by table-data writes is automatically accepted by the equivalent Search2 filter. Validate the combinations used by a client against the actual column types. An incompatible comparison fails the request; it does not simply produce zero matches.

This also means ordering follows PostgreSQL semantics for the column type, while filtering a text column uses lexical comparison. For example, text values 10, 2, and 30 sort as 10, 2, 30, not numerically.

Combining filters

Repeated filters on the same column are allowed and are still joined with AND. This can express a half-open interval:

amount greater than 100
amount less than 200

It cannot express alternatives such as status = draft OR status = issued. Search2 has no OR operator or value-list filter.

The optional broad text condition is also ANDed with every column filter. Use it to narrow an already structured result, not as a separate alternative route to a match.

Ordering

Search2 accepts at most one order column and one direction. The order column must be a public column name or an allowed server column; physical names are rejected.

• Ascending is the default when an order column is supplied.
• Descending is used only when explicitly requested.
• Without an order column, rows use descending ID.

Search2 adds no secondary order. When several rows have the same ordered value, their relative order is unspecified and can change between requests. This makes stable pagination impossible even aside from the missing offset.

PostgreSQL’s default null placement applies: nulls come last in ascending order and first in descending order.

Practical choices

• Filter codes and controlled text with Equals when case-sensitive equality is correct.
• Use Contains, Starts with, and Ends with only for text columns and account for SQL wildcards.
• Use Is null rather than comparing to an empty string when the value is actually absent.
• Test range and inequality filters against the actual database type before depending on them in a client.
• Choose an order column with mostly unique values when stable-looking results matter.
• Use Search instead when the requirement is ranked or typo-tolerant text discovery.

Return to How Search2 works for table scope, permissions, counts, and returned row behavior.