Implicit Conversion Breaks Index

SELECT * FROM orders WHERE order_id = '12345'; -- STRING vs INTEGER column

SELECT * FROM orders WHERE order_id = 12345; -- match column type

Comparing a VARCHAR literal to an INTEGER column forces the database to convert EVERY row before filtering — the index is useless. 5M rows: 8,000ms vs 80ms. Always match your literal to the column type.

Critical

FLOAT for Financial Calculations

CREATE TABLE transactions (amount FLOAT); -- 0.1 + 0.2 = 0.30000000000000004

CREATE TABLE transactions (amount DECIMAL(19,4)); -- exact to 4 decimal places

FLOAT uses base-2 binary representation and cannot exactly store most base-10 decimals. $123.45 × 1.075 = $132.70875000000001 with FLOAT — audit failures and customer overcharging. Use DECIMAL for any value humans care about being exact.

Critical

TIMESTAMP Without Timezone in Global Apps

created_at TIMESTAMP DEFAULT NOW() -- no timezone: ambiguous for global users

created_at TIMESTAMPTZ DEFAULT NOW() -- UTC internally, converts on read

TIMESTAMP stores a local time with no context. When your app servers are in different regions or DST kicks in, "2024-03-10 02:30" becomes ambiguous. TIMESTAMPTZ stores UTC always and converts to any timezone on display.

High

CAST Without TRY_CAST in ETL Crashes Pipeline

INSERT INTO clean SELECT CAST(raw_amount AS DECIMAL(10,2)) FROM staging; -- crashes on 'N/A'

INSERT INTO clean SELECT TRY_CAST(raw_amount AS DECIMAL(10,2)) FROM staging; -- NULL on bad row

A single malformed value (empty string, "N/A", "null") causes CAST to throw an error and kill the entire INSERT. TRY_CAST returns NULL for bad rows, letting the pipeline continue. Quarantine NULLs separately for human review.

High

Converting the Column Instead of the Literal

WHERE CAST(employee_id AS VARCHAR) = '100' -- converts 5M column values, breaks index

WHERE employee_id = CAST('100' AS INTEGER) -- converts one literal, index intact

When you wrap the column in a function or CAST, the database cannot use the index on that column. Wrap the literal (constant) value instead — it is computed once, not per row.

High

INT When BIGINT Is Already Needed

user_id INTEGER -- max 2,147,483,647; Twitter exceeded this in 2010

user_id BIGINT -- max 9,223,372,036,854,775,807

INTEGER overflows at 2.1B. Once a sequence hits the max, INSERTs fail with "integer out of range". Migrating a primary key column to BIGINT after the fact on a busy table requires downtime and reindex. Choose BIGINT for any ID that could scale past ~1B.

Medium