A predicted ICD-10-CM code can be wrong yet taxonomically near the reference code—or wrong in a very different part of the classification. To score that distinction, pin a specific code-set release, represent its parent-child relationships explicitly, and define what “near” means for your evaluation. PostgreSQL can traverse a hierarchy, but neither SQL nor the code set supplies a universally correct proximity score.
Choose what “close” means before writing SQL
Exact-match accuracy treats every nonmatching prediction alike. A proximity metric instead assigns a distance or score based on a chosen relationship between the predicted and reference codes. That can be useful for model evaluation, but the result is an evaluation design—not an official CMS score or, by itself, evidence of clinical equivalence.
As an Amazon Associate I earn from qualifying purchases.
For a simple tree, one possible distance is the number of parent-child edges on the path between two codes. The path can be found through their lowest common ancestor: walk each code toward its ancestors, identify the first shared ancestor, then count the edges from each code to that ancestor and add them. This makes a parent-child pair one edge apart and a pair of siblings two edges apart. These are consequences of that proposed metric, not official judgments about the clinical significance of those differences.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Before implementing a metric, decide and document:
- Exact matches: Are they distance zero, or a score of one when using a similarity scale?
- Hierarchy relationships: How should ancestor-descendant pairs compare with siblings or codes in separate branches?
- Edge costs and direction: Does every parent-child step cost the same, and should moving upward count differently from moving downward?
- Scale: Will results be raw distances, capped distances, or normalized scores? State the range and which direction is better.
- Problem inputs: How will invalid codes, missing predictions, and codes from different releases be handled?
- Aggregation: How will per-example scores be summarized, and will exact-match accuracy also be reported?
A distance alone does not establish that a predicted diagnosis is clinically acceptable. If scores will influence model comparisons or clinical workflows, compare plausible metrics on representative, human-reviewed cases and examine where their rankings differ.
#1 Best Overall
Pin the ICD-10-CM release
“ICD-10” can refer to different code sets. The example here concerns the U.S. clinical modification for diagnoses, ICD-10-CM—not the separate ICD-10-PCS procedure files. CMS lists FY 2027 ICD-10-CM files for encounters and discharges from October 1, 2026 through September 30, 2027; CDC gives the same service period. Check the official CMS ICD-10 page or CDC ICD-10-CM files when selecting files, since release timing changes.
Keep the release identifier with every imported code and evaluation result. A future code-set update must not silently change the relationships used to score old predictions. Import the official files for the release relevant to the evaluation, preserve source fields needed to reproduce parent relationships and descriptions, and validate the imported structure before calculating distances.
Represent the hierarchy in PostgreSQL
A relational adjacency list is a practical baseline: store a stable code identifier, release identifier, parent code, and description, with a foreign key connecting a parent to a code in the same release. The explicit parent relationship gives a recursive query something meaningful to traverse; it is safer than inferring hierarchy from punctuation or character prefixes.
The Tool Desk
Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →PostgreSQL describes recursive queries as typically used for hierarchical or tree-structured data. Its PostgreSQL 18 documentation for WITH queries covers recursive CTEs, termination, and ways to compute depth-first or breadth-first sort keys. A recursive CTE can walk a code toward its ancestors or descendants. The recursive term needs a stopping condition; if the imported relationships might contain cycles, add cycle protection appropriate to the data. Do not rely on implicit row order when ordering matters.
Another option is PostgreSQL’s ltree extension, which stores dot-separated label paths and provides operations for searching trees. It is useful when codes map cleanly to stable hierarchy paths and ancestor or descendant queries are common. The ltree documentation sets a maximum of 1,000 characters per label and 65,535 labels per path; these are PostgreSQL type limits, not ICD limits.
Choose between an adjacency list and path storage based on import and update complexity, query patterns, indexing needs, and whether the code set’s relationships fit a simple tree. A path representation can make some hierarchy queries convenient; an adjacency list keeps parent links explicit and can be traversed recursively. Neither is inherently faster for your workload without measurements on the actual schema and data.
Do not substitute character edits for hierarchy distance
PostgreSQL’s fuzzystrmatch extension provides Levenshtein distance: the number of insertions, deletions, and substitutions needed to transform one string into another, with configurable operation costs. It can help compare textual typos, but it does not measure distance in a classification tree. ICD-10-CM code characters and punctuation represent classification labels, so a small string edit need not mean a close taxonomic or clinical relationship. Use Levenshtein only when textual similarity is the question; use an explicitly defined hierarchy metric when taxonomy is the question. See the PostgreSQL fuzzystrmatch documentation.
Build and check a reproducible scoring pipeline
- Import a named release. Use official files from CMS or CDC, record the fiscal year and source, and retain fields needed to reproduce parent links and descriptions.
- Validate the imported relationships. Check code uniqueness within a release, parent references, and the release files’ conventions for terminal or leaf codes. Do not assume every syntactically plausible string is a valid billable code.
- Implement the declared metric. Use a recursive CTE or a database function to traverse the imported relationships. Enforce release matching and define explicit behavior for invalid, missing, or cross-release inputs.
- Test deliberate cases. Create fixtures for exact matches, parent-child pairs, siblings, distant branches, invalid codes, and cross-release pairs. Treat these as tests to run against your implementation—not as proof that a rubric is clinically valid.
- Report enough to reproduce results. Publish the release, distance or similarity definition, normalization and aggregation rules, and treatment of problematic inputs alongside evaluation results. If model rankings matter, compare at least two plausible metrics on human-reviewed examples and report cases where rankings change.
These steps establish a reproducible database calculation, not a validated clinical scoring rubric. No independently established performance benefit or clinical validity follows simply from implementing the distance in SQL.
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.
