Grouping & aggregates

Grouping and deduplication key on the equality term, never on the stored ciphertext, so they work on the same variants as =: _eq, every _ord variant, and the text_search variants. MIN / MAX need an ordering term (any _ord variant, or text_search). Arithmetic aggregates don't work at all — that's the last section. As everywhere, operands and call-site casts must be typed; see Core concepts.

GROUP BY and DISTINCT

Encryption is non-deterministic: the same plaintext encrypts to a different ciphertext every time. An encrypted column is a domain over jsonb, and Postgres compares a domain using the base type's comparison — so the natural forms compare whole encrypted payloads, never the plaintext behind them. They raise no error; they just answer wrongly.

SELECT email, COUNT(*) FROM logins GROUP BY email;   -- one group per row
SELECT DISTINCT email FROM logins;                   -- every row comes back

Key on the equality term instead. It's a deterministic keyed hash, identical for equal plaintexts:

SELECT COUNT(*)
  FROM logins
  GROUP BY eql_v3.eq_term(email);

SELECT DISTINCT ON (eql_v3.eq_term(email)) email
  FROM logins
  ORDER BY eql_v3.eq_term(email);

Plain DISTINCT has nowhere to put the term, so deduplication is DISTINCT ON. Postgres requires the leading ORDER BY expression to match the DISTINCT ON expression, and the row that survives each group is the first one in that order — add further sort keys after it to choose which.

The term is an HMAC — opaque and one-way — so there's rarely a reason to select it. DISTINCT ON puts no restriction on the projection, so the encrypted column comes back alongside it for the client to decrypt; under GROUP BY that takes eql_v3.grouped_value.

Keying on the extractor is also what makes the query fast. GROUP BY email would use the entire encrypted payload — 1–2 KB per row — as the hash key: Postgres estimates a hash table far larger than the default work_mem and falls back to a disk-spilling GroupAggregate. The extractor key is small, so the hash table fits in work_mem and the planner picks HashAggregate reliably. The same reasoning, from the index-tuning angle, is in Indexes.

eql_v3.grouped_value

Grouping by the term costs you the column: Postgres rejects a bare reference to it, because it can't prove the column is constant within each group.

SELECT email, COUNT(*)
  FROM logins
  GROUP BY eql_v3.eq_term(email);

Every row in a group encrypts the same plaintext, so any one of them represents the group. eql_v3.grouped_value is the aggregate that hands one back:

SELECT eql_v3.grouped_value(email) AS email, COUNT(*)
  FROM logins
  GROUP BY eql_v3.eq_term(email);

What comes back is a real encrypted payload, which the client decrypts as usual — unlike the group key itself, which is a term and can never be decrypted.

eql_v3.grouped_value(jsonb) RETURNS jsonb

One aggregate covers every encrypted type: all the domains are jsonb-backed, and grouped_value returns its input unchanged — it never decrypts, inspects, or compares the value. Three details worth knowing:

• It returns the first non-NULL value in each group. Which value is "first" is arbitrary without an intra-aggregate ORDER BY, and not deterministic under parallel aggregation — that's fine here, because every member of the group is an encryption of the same plaintext.
• A group of all-NULL values aggregates to NULL, matching native aggregate semantics.
• It's PARALLEL SAFE with a combine function, so it survives partial aggregation on large GROUP BY workloads.

Deduplicating without a per-group aggregate needs none of this — that's DISTINCT ON, above.

COUNT and COUNT(DISTINCT)

Plain COUNT(col) counts non-NULL rows — it never compares values, so it works on any variant, including storage-only ones:

SELECT COUNT(tax_id) FROM users;   -- works even on bare public.eql_v3_text

COUNT(DISTINCT col) deduplicates, so it has the ciphertext problem too: on the raw column it counts distinct ciphertexts, which is just the row count. Count distinct terms instead:

SELECT COUNT(DISTINCT eql_v3.eq_term(email)) FROM logins;

MIN and MAX: eql_v3.min / eql_v3.max

EQL ships min / max aggregates per ord-capable variant of every scalar type. The input type selects the aggregate, and the return type matches the input:

eql_v3.min(public.eql_v3_<T>_ord)      RETURNS public.eql_v3_<T>_ord
eql_v3.max(public.eql_v3_<T>_ord)      RETURNS public.eql_v3_<T>_ord
eql_v3.min(public.eql_v3_<T>_ord_ope)  RETURNS public.eql_v3_<T>_ord_ope
eql_v3.max(public.eql_v3_<T>_ord_ope)  RETURNS public.eql_v3_<T>_ord_ope
eql_v3.min(public.eql_v3_<T>_ord_ore)  RETURNS public.eql_v3_<T>_ord_ore
eql_v3.max(public.eql_v3_<T>_ord_ore)  RETURNS public.eql_v3_<T>_ord_ore

Comparison routes through the variant's < / > operator on the ordering term — no decryption happens in the database, and the result is an encrypted value the client decrypts. NULL inputs are skipped; an all-NULL input set returns NULL, matching native aggregate semantics.

SELECT eql_v3.min(salary) FROM users;
SELECT eql_v3.max(salary) FROM users WHERE department = 'engineering';

-- Combined with grouping: grouped_value returns the department code encrypted,
-- so the client can decrypt it to label the row.
SELECT eql_v3.grouped_value(department_code) AS department_code,
       eql_v3.max(salary)
  FROM users
  GROUP BY eql_v3.eq_term(department_code);

If the column is generic jsonb rather than a domain, cast to the right variant at the call site so overload resolution can pick the aggregate:

SELECT eql_v3.min(salary_jsonb::public.eql_v3_bigint_ord) FROM users;

A btree on eql_v3.ord_term(col) serves MIN / MAX — the Indexes page has the recipe.

No SUM, no AVG

Leaves inside encrypted JSON

Leaves extracted from an encrypted JSON document (metadata -> 'region_selector'::text, a public.eql_v3_json_entry) cannot be grouped by equality. Entries stopped carrying an equality term in EQL 3.0.1: exact matching on a JSON field became presence of a value selector, which containment answers as a filter — there is no per-field key to hash rows into groups with.

What still works on an extracted leaf is ordering: eql_v3.min / eql_v3.max on String and Number leaves, via their CLLW OPE term.

SELECT eql_v3.min(metadata -> 'total_selector'::text) FROM orders;

To group by a field, promote it to its own encrypted column — a text_eq (or any _ord) column alongside the document — and group on that column's equality term as above. Selectors, value selectors, and node capabilities are in JSON.

Where to go next

• Indexes — the hash/btree recipes that back these shapes, and the full work_mem / HashAggregate story.
• Joins — equality terms across tables, and the same-keyset rule.
• Filtering — the WHERE shapes that feed these aggregates.