To grade an incorrect ICD-10-CM prediction by how far it is from the reference, choose a specific code-set release, represent its parent–child relationships, and define a distance function over that hierarchy. PostgreSQL can traverse the relationships with recursive common table expressions (CTEs), but neither PostgreSQL nor CMS prescribes what a proximity score should mean. The method below is an evaluation design—not a clinically validated scoring standard.
What a proximity score measures—and what it does not
Exact-match grading gives a prediction either a match or a miss. A hierarchy-based distance adds information: it can distinguish a code one parent–child edge away from a code several branches away. That can be useful when evaluating predictions, provided the score reflects the distinctions that matter for the evaluation.
This article concerns the U.S. ICD-10-CM diagnosis hierarchy, not the separate ICD-10-PCS procedure code set. CMS lists diagnosis and procedure files separately on its ICD-10 codes page.
A score measures proximity under the chosen hierarchy and metric. It does not establish that two diagnoses are clinically equivalent, that a prediction is safe, or that a model performs well in a clinical workflow. No CMS or PostgreSQL standard defines an ICD-10-CM proximity score.
Do these 3 things before closing this tab:
1Repair Windows errors before they cause bigger problems2Scan for outdated or missing drivers - takes under a minute3Clear out junk files and repair common Windows errors#1 Best Overall
Pin the release before building the hierarchy
Code sets change. Store the release identifier with each imported code and evaluation so a later update cannot silently change the relationships or ground truth behind a result. As of October 5, 2026, CMS and CDC list FY 2027 ICD-10-CM files for encounters and discharges from October 1, 2026 through September 30, 2027. Confirm the current files and service period on the CMS ICD-10 page and the CDC ICD-10-CM files page before importing; release timing is subject to change.
Import the official files for the selected fiscal year and preserve their release metadata and fields needed to reproduce descriptions and parent relationships. Validate code uniqueness, parent references, and terminal or leaf conventions against those files. A string that looks syntactically plausible is not necessarily a valid or billable code.
Choose a representation that matches the code relationships
A straightforward relational baseline is an adjacency list: one row per code per release, with a stable identifier, release identifier, parent code, and description. A foreign key from the parent to the code table can help enforce that parent references exist. Keep release identity in the relationship as well; a parent from one release must not be joined accidentally to a code from another.
Rank #2
PostgreSQL offers two useful approaches for traversing this structure. Neither should be assumed to model every official relationship correctly until the selected release data and its edge cases are checked.
Outdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchWindows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallAdjacency list with recursive CTEs
A recursive CTE can walk from a code toward its ancestors or descendants. This keeps parent relationships explicit and accommodates hierarchy changes through data updates. PostgreSQL describes recursive queries as commonly used for hierarchical or tree-structured data in its PostgreSQL 18 WITH Queries documentation.
For illustration, assume a table named icd10cm_code with release_id, code, and nullable parent_code. This query returns a prediction’s ancestor chain, starting with the prediction itself:
WITH RECURSIVE ancestors (release_id, code, parent_code, depth, visited) AS (
SELECT release_id, code, parent_code, 0, ARRAY[code]
FROM icd10cm_code
WHERE release_id = $1 AND code = $2
UNION ALL
SELECT parent.release_id,
parent.code,
parent.parent_code,
child.depth + 1,
child.visited || parent.code
FROM ancestors AS child
JOIN icd10cm_code AS parent
ON parent.release_id = child.release_id
AND parent.code = child.parent_code
WHERE NOT parent.code = ANY(child.visited)
)
SELECT code, depth
FROM ancestors
ORDER BY depth;The example uses a visited-code array as cycle protection and limits traversal to a single release. A missing starting code returns no rows. Production SQL should also enforce a clear termination condition and handle malformed or unexpectedly cyclic imported relationships explicitly. PostgreSQL warns that a recursive term must eventually stop producing rows; it also documents how to compute explicit depth-first or breadth-first sort keys rather than relying on implicit row order in the recursive query documentation.
Path storage with ltree
The PostgreSQL ltree extension stores dot-separated label paths and provides operations for finding ancestors and descendants. It can be convenient when codes map cleanly to stable hierarchy paths and those path queries are frequent. Its documented limits—1,000 characters per label and 65,535 labels per path—are PostgreSQL type constraints, not limits of ICD-10-CM. See the ltree documentation.
Free tools Windows power users keep installed
One-click scans. No signup required.
Choose between a path representation and an adjacency list by considering import and update complexity, query patterns, indexing, and whether the code set is actually a simple tree. Measure performance against your own schema and workload; the documentation does not establish that one approach is faster for ICD-10-CM.
Define the distance before writing the score
If the selected relationships form a tree, one possible metric is the number of parent–child edges on the path between two nodes. Find the nodes’ lowest common ancestor (LCA), then add the number of edges from the prediction to that ancestor to the number from the reference to it:
distance(prediction, reference) = depth(prediction to LCA) + depth(reference to LCA)
Under this definition, a code and itself have distance 0; a direct parent–child pair has distance 1; and sibling nodes sharing a parent have distance 2. Those values follow from the proposed edge-count metric, not from an official ICD-10-CM scoring rule. Confirm that the selected release’s relationships support a tree interpretation before treating this formula as authoritative.
Distance alone is not a complete grading policy. Decide what the number means to a reader or downstream system: a lower distance is closer, but does a score need to run from 0 to 1, or should it remain an unbounded edge count? If you normalize it, specify the denominator and how maximum distance is determined. Consider whether every edge should cost the same and whether direction matters—for example, whether predicting a parent should be treated differently from predicting a descendant.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Do not substitute code-string edit distance
PostgreSQL’s fuzzystrmatch extension includes Levenshtein distance, which counts insertions, deletions, and substitutions between strings under configurable costs. That can help measure textual typos, but it does not measure taxonomy distance. ICD code characters and punctuation encode classification labels; a small string edit can cross a meaningful hierarchy boundary, while two codes that are taxonomically close may not be the closest strings. Use Levenshtein only when textual similarity is the intended target, not as a proxy for hierarchy proximity. See PostgreSQL’s fuzzystrmatch documentation.
Specify edge cases and aggregation rules
Write down the behavior before comparing models or reporting results. At minimum, settle these policy choices:
- Exact matches: State whether exact matches receive distance 0 and how any normalized score represents them.
- Ancestors, descendants, and siblings: State how each relationship is counted, including whether direction changes the penalty.
- Edge costs: Say whether all parent–child edges have equal cost or whether a different weighting scheme is used.
- Range and direction: Define the minimum and maximum, if bounded, and whether lower or higher values indicate a better prediction.
- Invalid and missing codes: Decide whether they are rejected, recorded as unscorable, or assigned a separate outcome; do not silently treat them as ordinary distant nodes.
- Release mismatches: Decide how a prediction and reference tied to different releases are handled. Do not traverse across releases as if their relationships were interchangeable.
- Aggregation: Specify how per-example distances become a reported result, including how unscorable examples affect the denominator.
These decisions are part of the metric. SQL implements them; it cannot choose their clinical or evaluation meaning for you.
Validate the implementation before trusting results
Build small fixtures with known relationships and check the returned distances before running a large evaluation. Include exact matches, parent–child pairs, siblings, distant branches, invalid codes, and cross-release cases. These are recommended checks, not evidence that a metric is clinically valid.
- Import one named fiscal-year release and retain the source data needed to reproduce each parent relationship.
- Check for duplicate codes within a release, unresolved parent references, unexpected cycles, and terminal or leaf conventions.
- Run the selected traversal and distance definition on hand-constructed fixtures; verify the expected output and behavior for missing inputs.
- Compare at least two plausible metrics on representative human-reviewed cases if the result will be used for model comparison or a clinical workflow decision. Inspect ranking changes and edge cases.
- Report the release, metric definition, normalization, direction, and aggregation rule alongside every published score.
PostgreSQL supplies traversal and storage primitives, not validation of the scoring rubric. A convenient query or a plausible ranking does not by itself show that the score reflects coding quality or improves clinical decisions.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
