Key takeaway
Preserving string bytes does not preserve the database’s equality semantics. Test the actual collation and retain source identities when the receiving comparison treats different strings as equal.
Inspect what equal means at the receiving query
A transfer preserves every identifier’s text, yet a receiving summary reports fewer distinct identifiers. The file can be byte-faithful while a database comparison deliberately ignores case or accents. Investigate the comparison rule before assuming the exporter dropped records. The question is what the receiving column or expression considers equal, rather than whether the characters survived decoding.
PostgreSQL’s collation documentation distinguishes deterministic comparisons, which retain a bytewise tie break, from nondeterministic comparisons that can treat different byte sequences as equal. The provider and settings determine the insensitive behavior; changing the deterministic flag alone does not select every desired comparison. Record the actual database version, provider, locale, options and expression used.
A hypothetical three-key receiving fixture
Assume the source owner defines P-7, p-7 and P-8 as three distinct identifiers. Their bytes are preserved in a synthetic receiving fixture. One proposed comparison uses PostgreSQL’s documented custom ICU case-insensitive profile, und-u-ks-level2 with deterministic=false. Another retains byte-distinct equality. These are stipulated comparison specifications, not results of a database run performed for this article.
Under the specified case-insensitive profile, P-7 and p-7 belong to the same equality group while P-8 is separate. Under byte-distinct equality, all three remain different. That predicts two versus three groups in this fixture. The completed preparation decision is to withhold source-identity deduplication until an actual receiving test demonstrates the agreed rule. Searching across case variants and merging source records are different operations.
| Illustrative pair | Byte-distinct equality | Specified case-insensitive equality | Source-identity treatment |
|---|---|---|---|
| P-7 / p-7 | Different | Equal | Keep distinct under stipulated source contract |
| P-7 / P-8 | Different | Different | Keep distinct |
| P-7 / P-7 | Equal | Equal | Check record/event identity before deduplication |
Treat language settings as part of the method
ICU describes comparison strengths and locale tailoring. At common strengths, case and accents can be treated at different levels; language-specific treatment matters. A broad label such as Unicode comparison is therefore insufficient to reproduce behavior. Record the precise profile and test the actual strings relevant to the archive, including distinctions the source uses for identifiers.
PostgreSQL also notes provider-version and platform dependencies for collations. An environment change can therefore matter even if the exported file is identical. Keep the comparison specification with the query and environment evidence. Do not copy an example locale into production merely because its result seems convenient for three test strings. The source owner and receiving engineer must agree what constitutes the same key.
Test the operations that consume equality
Use authorized synthetic fixtures to exercise the actual join, grouping, distinct-count and duplicate-handling paths. State expected row counts and unmatched cases before execution, then preserve the result and settings. If a receiving load rejects or merges a key, capture that behavior rather than treating a successful load message as proof that the source identities survived.
The three-key example also exposes why a join can change meaning without altering text. A lookup for P-7 under an insensitive comparison may match both P-7 and p-7. If the source contract treats them as different entities, that comparison is unsuitable for that identity join even if it is helpful in a search box. Resolve ambiguous matches explicitly; do not let an interface’s matching convenience define the archive’s entity structure.
Keep original identity and search behavior separately reviewable
The final fixture specification retains the three source identifiers and permits investigation of a separate case-insensitive search operation. It does not authorize merging records or recommend changing a live database’s collation. The next evidence needed is a reproducible receiving test with the agreed source identity contract, actual expression and environment. A discrepancy remains open until that evidence explains it.
The Unicode preservation guide concerns decoding, normalization and separate source/derived strings. Here the strings can remain unchanged while query equality changes their grouping. Preserve both the representation invariant and comparison invariant in the handoff. That distinction lets an owner describe exactly what was tested and prevents a faithful text export from being mistaken for a verified identity mapping.