[mpwoz]

In this example, isn't the problem that you're declaring column A as an 'int' type when what you really wanted was a string? Column B would be the "correct" one for your use case I'd think, unless you also need numeric sorting behavior.

Is there a type in e.g. Postgresql that would let you store '000123', give you numeric sorting (000123 > 1), and still return the leading zero characters? AFAIK that doesn't exist but I'm not that familiar with DB types.

[banana_giraffe]

Yep. To be clear, the real problem where I shot myself in the foot was a column declared as an int, and stored hex strings. No one noticed the issue, because 100% of the time SQLite left the strings alone, or the string->int->string conversions where safe. Then came along a case where the hex string was all ints, and had leading zeroes that were dropped. Those leading zeroes had meaning, so wacky things happened.

And yep, it's all my fault for the column definition. Still an annoying surprise, esp since in this specific case, the surprise happened years after I screwed up.