Filtering

Every filter below is ordinary SQL — the encrypted operators resolve from the column's domain variant, and a functional index on the matching term extractor serves the predicate. One rule applies throughout: operands must be typed ($1::public.eql_v3_text_eq, not a bare literal), or PostgreSQL falls through to native jsonb semantics. See Core concepts for the typed-operand rule and how unsupported operators fail loudly instead of returning wrong rows.

Equality: = and <>

Works on _eq and every _ord variant of every scalar, and on text_search:

SELECT * FROM users WHERE email = $1::public.eql_v3_text_eq;
SELECT * FROM users WHERE tax_id <> $1::public.eql_v3_text_eq;

On _eq columns, and on every text variant, equality compares the HMAC (hm) term. On the _ord variants of the non-text scalars there is no hm: equality compares the ordering term instead, which is lossless over those domains, so those columns get = and <> for free. See Core concepts for the mechanism.

-- salary is public.eql_v3_bigint_ord: equality works without an hm term
SELECT * FROM users WHERE salary = $1::public.eql_v3_bigint_ord;

Bare storage-only variants (public.eql_v3_text, public.eql_v3_integer, …) block every comparison — see the type pages for what each variant supports: Numbers, Dates & times, Text, Booleans.

IN lists

IN desugars to =, so it needs the same equality-capable variants. Each list element is a separately encrypted, typed operand:

SELECT * FROM users
WHERE email IN ($1::public.eql_v3_text_eq, $2::public.eql_v3_text_eq, $3::public.eql_v3_text_eq);

There is no way to encrypt a list as one value — the client encrypts each element and binds it as its own parameter. IN (subquery) also works, subject to the same-keyset rule covered in Joins.

Ranges and BETWEEN

<, <=, >, >= work on _ord / _ord_ope variants and text_search, which carry an OPE (op) term, and on the pinned _ord_ore / text_search_ore variants, which carry a block-ORE (ob) term:

SELECT * FROM users WHERE salary >= $1::public.eql_v3_bigint_ord;

-- BETWEEN desugars to >= and <=
SELECT * FROM users
WHERE created_at BETWEEN $1::public.eql_v3_timestamp_ord AND $2::public.eql_v3_timestamp_ord;

Half-open ranges compose the same way:

SELECT * FROM events
WHERE occurred_at >= $1::public.eql_v3_timestamp_ord
  AND occurred_at <  $2::public.eql_v3_timestamp_ord;

Text fuzzy matching: @@

There is no LIKE on encrypted columns — encrypted free-text matching is bloom-filter fuzzy match via @@ on a text_match or text_search column:

SELECT * FROM users WHERE name @@ $1::public.eql_v3_text_match;

The client encrypts the search term into a bloom-filter query value; matching is probabilistic (false positives possible, false negatives not). @> / <@ raise on these domains — they were the old spelling of this match and are now blockers. For the full no-LIKE story, short-term behaviour, and match-term tuning, see Text.

JSON containment and path filters

Encrypted JSON documents (public.eql_v3_json_search) filter by containment and path existence:

-- Does the document contain this (encrypted) structure?
SELECT * FROM orders WHERE metadata @> $1::eql_v3.query_json;

-- Does this path exist in the document?
SELECT * FROM orders WHERE eql_v3.jsonb_path_exists(metadata, 'region_selector');

-- Exact match on a field: containment on a value selector, built by the client
SELECT * FROM orders WHERE metadata @> $1::eql_v3.query_json;

Field access is by selector hash, not plaintext path — and exact field matching is containment on a value selector, not = on an extracted leaf, which raises. The full JSON surface — containment, field access, path queries, and range filters on extracted leaves — is in JSON.

Combining predicates

Encrypted predicates compose with AND, OR, NOT, and parentheses like any other predicate — and plaintext columns filter normally alongside encrypted ones in the same WHERE clause:

SELECT * FROM users
WHERE status = 'active'                           -- plaintext column, native operator
  AND created_at >= $1::public.eql_v3_timestamp_ord      -- encrypted range
  AND (email = $2::public.eql_v3_text_eq                 -- encrypted equality
       OR name @@ $3::public.eql_v3_text_match);         -- encrypted fuzzy match

The planner treats each encrypted predicate independently, so it can combine an index on a plaintext column with a functional index on an encrypted one (bitmap-AND, or whichever plan is cheapest).

IS NULL and IS NOT NULL

A SQL NULL column value is never encrypted — there is no payload to encrypt — so null checks work on every variant, including storage-only ones:

SELECT * FROM users WHERE tax_id IS NULL;
SELECT * FROM users WHERE tax_id IS NOT NULL;

Don't confuse this with a JSON null inside an encrypted document, which is an encrypted value like any other — see JSON.

Shape summary

Every one of these has a full index recipe — which method, which extractor, and how to confirm the index engages with EXPLAIN — in Indexes.