Comparing INTEGER id to '42' stays index-friendly but VARCHAR code to 42 does not — why?

## Two mismatches that look identical and behave oppositely ```sql -- users.user_id INTEGER SELECT * FROM users WHERE user_id = '42'; -- parts.part_code VARCHAR(20) SELECT * FROM parts WHERE part_code = 42; ``` Both statements put a character value next to a numeric one. Standard SQL calls those types not mutually comparable, and engines that accept the statement must convert one side. The first query is typically harmless; the second is a classic production slowdown. The difference is entirely about *which operand gets converted*. ## How the direction is decided Oracle and SQL Server publish a data-type precedence list. When two operands of different types meet, the one with the lower precedence is converted to the higher. Numeric types sit above character types, so the character operand is always the one that changes. PostgreSQL takes a different route: an untyped quoted literal is resolved to whatever type the context demands, so `'42'` simply becomes an integer next to an integer column — while an already-integer literal next to a `varchar` column has no resolution at all and raises an error. MySQL converts both operands to floating point when a string column meets a number. Different mechanisms, same practical outcome: the constant is cheap to move, the column is not. ## Why converting a constant is free and converting a column is fatal A literal or bind value is one value. The engine converts it once, before the scan begins, and then compares the *stored* column values against it. The index on that column is an ordered structure over exactly those stored values, so it can be searched directly. A column conversion is an expression evaluated once per row. Two things break. First, the filter now tests a derived value that the index does not contain, so there is nothing to search. Second, the conversion usually does not preserve order — as text `'9'` sorts after `'12345'`, as numbers it does not — so the engine cannot even walk a contiguous slice of the index. It must read everything and convert everything. ## The rule: cast the literal, never the column ```sql -- BAD: explicit cast on the column, same damage as the implicit one SELECT * FROM parts WHERE CAST(part_code AS INTEGER) = 42; -- GOOD: make the constant match the column's declared type SELECT * FROM parts WHERE part_code = '42'; -- GOOD: explicit cast when you want the intent visible SELECT * FROM parts WHERE part_code = CAST(42 AS VARCHAR(20)); ``` The same rule governs parameters: bind `'42'` as a character parameter for a character column and as an integer for an integer column, rather than binding whatever type the application variable happens to have. ## Casting the column can also be wrong, not merely slow Suppose `part_code` holds `'X-99'` for some rows and a query reads `WHERE category = 'NUMERIC' AND CAST(part_code AS INTEGER) = 42`. It is tempting to argue that the first predicate excludes the unconvertible rows. SQL makes no such promise: the engine may evaluate the predicates in either order, or evaluate the cast while scanning before the category filter is applied. The statement can therefore fail with a conversion error on rows the query was never meant to return, and — worse — it can start failing later, when a plan change reorders the evaluation. Converting the constant has no such hazard, because there is exactly one constant and it either converts at parse time or it does not. ## When the real answer is the schema If the mismatch keeps recurring, the column type is wrong. A `VARCHAR` column that only ever holds integers and is only ever compared to integers should be an integer column: the comparison becomes natural, the index shrinks, and the database starts rejecting garbage instead of storing it. If the column genuinely holds mixed codes, then the *callers* are wrong to compare it to numbers, and the fix is to correct the literals and the bindings. ## What to check Read the predicate text the plan prints, not just the operator name. A conversion function wrapped around a column — `CONVERT_IMPLICIT(...)`, `TO_NUMBER(...)`, `(part_code)::integer` — is the tell, and it applies equally whether you wrote the cast or the engine did.