Text

Text is the richest encrypted scalar. Beyond the variants every scalar type gets, text adds its own: text_match for encrypted free-text matching, and text_search for columns you need to look up, sort, and search. Emails, names, tax IDs, addresses — this page is the full surface for all of them.

Variants

All of these are jsonb-backed domains. Which one you declare fixes the column's query capability — the variant model itself is covered in Core concepts:

Declare only the capabilities you query on — each capability stores extra searchable material with defined leakage (see Searchable encryption).

Example

A users table mixing the variants by how each column is queried:

CREATE TABLE users (
    id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email   public.eql_v3_text_search,   -- lookup, sort, and free-text match
    name    public.eql_v3_text_match,    -- free-text match only
    tax_id  public.eql_v3_text_eq,       -- exact lookup only
    notes   public.eql_v3_text           -- store and decrypt only
);

SEM specifiers

Text takes the same mechanism specifiers as the other orderable types (the concept is defined in Core concepts):

text_ord and text_ord_ope are byte-identical today. Pin _ord_ope when you want a column's mechanism frozen against a future change of the default.

Payload

A value for a text_search column carries the shared envelope keys (v, i, c — see Core concepts) plus all three index terms:

{
  "v": 3,
  "i": { "t": "users", "c": "email" },
  "c": "mBbKmsMM%bK#QQOx1yLDBHyD...",
  "hm": "9c8ec1d2f9932b979b1bf3f09f8a4e2f6a41f8de2f0c8b7a52e1f5c3d4b6a790",
  "op": "5f2b1a9e4c07d38b6ea15c92...",
  "bf": [42, 1290, -8113, 30201]
}

• hm — equality term: WHERE email = $1 compares this
• op — ordering term: a hex-encoded CLLW OPE ciphertext, which ORDER BY and range comparisons sort by native bytea comparison
• bf — bloom-filter term: @@ fuzzy match tests these bit positions

A text_search_ore payload carries ob in place of op: an array of block-ORE ciphertexts rather than a single string.

The narrower variants carry only their own terms. A text_eq payload carries hm alone, text_match carries bf alone, and the orderable variants carry hm plus their ordering term (op for text_ord / text_ord_ope, ob for text_ord_ore). Text keeps hm on every orderable variant, because ordering over text is not equality-lossless the way it is over the numeric scalars: = and <> resolve against the HMAC term rather than the ordering term. A payload missing its variant's required term fails the domain CHECK at write time.

bf positions are signed: EQL stores the filter as PostgreSQL smallint[], and filters sized above 32768 emit upper-half bit positions as negative signed values. Consumers must use a signed 16-bit integer type.

Operators

Blocked operator cells raise an operator … is not supported exception — they never silently return wrong rows. ORDER BY is the one blocked cell that doesn't raise: it isn't an operator, so sorting a variant without an ordering term runs — but the order is meaningless (see Sorting). Operands must be typed ($1::public.eql_v3_text_eq), or PostgreSQL resolves the native jsonb operator instead of the encrypted one. Both rules are covered in Core concepts.

Functions

Every operator has a function form, for managed platforms that disallow custom operators — same typed arguments, identical resolution. Each lists the encrypted domains it applies to; the MIN / MAX aggregates only exist as functions.

eql_v3.eq(a, b)

SELECT * FROM users
WHERE eql_v3.eq(email, $1::public.eql_v3_text_eq);

eql_v3.neq(a, b)

SELECT * FROM users
WHERE eql_v3.neq(email, $1::public.eql_v3_text_eq);

eql_v3.lt / lte / gt / gte

-- any of the four; ordering is the usual reason to index text
SELECT id, email FROM users
WHERE eql_v3.gt(email, $1::public.eql_v3_text_ord)
ORDER BY eql_v3.ord_term(email);

eql_v3.matches(a, b)

-- probabilistic fuzzy match on the bloom-filter term
SELECT * FROM users
WHERE eql_v3.matches(email, $1::public.eql_v3_text_match);

eql_v3.min(col)

-- compares ordering terms; result decrypts client-side
SELECT eql_v3.min(email) FROM users;

eql_v3.max(col)

SELECT eql_v3.max(email) FROM users;

There are no like / ilike function forms — encrypted text matching is eql_v3.matches on a text_match value.

There is no LIKE

LIKE and ILIKE (~~ / ~~*) raise on every encrypted-domain variant — including text_match and text_search. SQL pattern matching is meaningless on ciphertext. Encrypted text matching is bloom-filter fuzzy match — @@ on a text_match or text_search column:

SELECT * FROM users WHERE email LIKE '%alice%';

-- Encrypted free-text match
SELECT * FROM users WHERE email @@ $1::public.eql_v3_text_match;

-- Function form
SELECT * FROM users WHERE eql_v3.matches(email, $1::public.eql_v3_text_match);

@@ is probabilistic ngram-bloom matching — it reduces to array containment over two bloom filters built from the downcased 3-gram token sets, so a true may be a false positive and a false never is. It is not LIKE, and it is not containment: it is order- and multiplicity-insensitive. The client encrypts the search term into a bloom-filter query value.

Search terms too short to tokenise

A search string below the tokeniser's floor — a 2-character term, say — produces no 3-grams and encrypts to an empty bloom (bf: []). Because an empty array is contained by every array, such a needle once matched every row in the table, silently. Since EQL 3.0.3 the match is guarded, giving LIKE ''-shaped semantics:

Only the second row changed: an empty needle now matches only a value whose own bloom is also empty. Nothing to rewrite, and values legitimately stored with an empty bloom are unaffected. On EQL 3.0.2 and earlier, reject sub-floor search terms client-side — the SDK already does.

Where to next