SQL Data Types

DECIMAL (also called NUMERIC) stores numbers as exact decimal digits — DECIMAL(10,2) stores exactly 10 significant digits with exactly 2 after the decimal point. Arithmetic on DECIMAL values produces exact results. 0.1 + 0.2 in DECIMAL is exactly 0.3. FLOAT stores numbers in binary floating-point format — the IEEE 754 standard used by all modern CPUs. Binary floating-point cannot represent most decimal fractions exactly. 0.1 stored as FLOAT is actually 0.1000000000000000055511151231... — a tiny error that compounds through arithmetic.

For money, FLOAT is never acceptable. Financial calculations require exact decimal arithmetic — $10.50 × 1,000,000 must be exactly $10,500,000.00, not $10,499,999.99 due to floating-point approximation. In a payment system processing millions of transactions, FLOAT errors accumulate into accounting discrepancies that violate regulatory requirements. The SEC and IRS require exact financial records. FLOAT violates this requirement at scale.

Always use DECIMAL for monetary values, prices, tax rates, and any financial calculation. The typical choices: DECIMAL(10,2) for amounts in dollars (up to $99,999,999.99), DECIMAL(5,4) for tax rates and percentages stored as decimals (0.0800 for 8% sales tax), DECIMAL(15,2) for very large institutional amounts. Use FLOAT only for scientific measurements, machine learning weights, and statistical values where exact decimal representation is neither possible nor required — the measurement itself has inherent imprecision that exceeds the floating-point error.

The functional difference between VARCHAR and TEXT varies by database. In PostgreSQL, VARCHAR and TEXT have identical storage and performance — there is no technical advantage to TEXT over VARCHAR or vice versa. The choice is entirely about documentation and constraints: VARCHAR(n) enforces a maximum length limit and communicates to any developer that the field should not exceed n characters. TEXT has no upper bound and makes no statement about expected length.

In MySQL, the distinction is more significant. VARCHAR(n) is stored inline with the row for values up to 65,535 bytes. TEXT types (TEXT, MEDIUMTEXT, LONGTEXT) are stored off-row as a reference, have different indexing limitations (cannot be fully indexed without a prefix), and have different performance characteristics for large values. For MySQL, prefer VARCHAR(n) for all fields where a reasonable maximum exists, and TEXT only for genuinely long content.

Professional guidance: use VARCHAR(n) whenever you can define a meaningful maximum — email VARCHAR(255), first_name VARCHAR(100), city VARCHAR(100), product_name VARCHAR(200). The n value should be based on the realistic maximum for the domain, not defaulted to 255 for everything. Use TEXT for genuinely unbounded fields: product descriptions, user reviews, blog content, log messages, any field where the author might reasonably write hundreds or thousands of words. The VARCHAR(n) limit serves as a soft validation and a schema communication tool — it tells the next developer what to expect from this column.

Phone numbers are not numeric values in the mathematical sense — they are identifiers that happen to use digits. Storing them as INTEGER or BIGINT causes three problems. First, leading zeros are lost. Many countries' local numbers start with a 0 dialling prefix — 020 7946 0958 in the UK stored as BIGINT becomes 2079460958, irreversibly losing the leading zero. Stored data can never be recovered to the original format.

Second, non-numeric characters cannot be stored. International format phone numbers include country codes (+1, +44), separators (-), and sometimes extensions (x204). BIGINT accepts none of these — any format beyond pure digits requires VARCHAR. Third, no arithmetic is ever performed on phone numbers — you never add two phone numbers together or calculate the average phone number. The entire reason to choose a numeric type is to enable arithmetic and range operations. Phone numbers need neither.

The correct type is VARCHAR(20) — long enough for any international format (+1-800-555-0199 is 15 chars, with headroom). This applies to any "number" that is really an identifier: EIN (12-3456789 — dash-formatted, no arithmetic), SSN (11 chars with dashes, no arithmetic), bank routing numbers, postal codes (some have letters — Canadian postal codes like K1A 0B1). The rule: if you would never multiply it, average it, or compare it with greater-than, store it as VARCHAR.

TIMESTAMP (TIMESTAMP WITHOUT TIME ZONE) stores a date and time value with no timezone information attached. The value is stored and retrieved exactly as entered — 2024-01-15 14:30:00 is stored and returned as-is. The database makes no attempt to convert it. If two users in different timezones insert the "same" timestamp, they might both insert 14:30:00 but mean completely different moments in absolute time (PST vs EST vs UTC).

TIMESTAMPTZ (TIMESTAMP WITH TIME ZONE) stores a specific moment in absolute time. The value is always stored internally as UTC. When you insert a timestamp with a timezone (or the database's configured timezone is used), it converts to UTC for storage. When you retrieve it, PostgreSQL converts from UTC to the current session's timezone. 2024-01-15 06:30:00-08:00 (PST) and 2024-01-15 14:30:00+00:00 (UTC) refer to the same moment and store identically.

Use TIMESTAMPTZ for all production application timestamps — created_at, updated_at, logged_at, processed_at. Any event that "happened at a specific moment" needs TIMESTAMPTZ to be unambiguous across timezones. Use TIMESTAMP only when the time has no timezone meaning — a scheduled broadcast at 20:00 every evening regardless of timezone, a business hours definition, a recurring calendar event. Most single-region startups operate in a single timezone (PST or EST) but use TIMESTAMPTZ anyway because it is the safer default and costs nothing extra. If you later add international users or move servers between regions, TIMESTAMP values become ambiguous while TIMESTAMPTZ remains correct.

CAST converts a value from one data type to another. The standard SQL syntax is CAST(expression AS target_type). PostgreSQL also supports the :: shorthand: expression::target_type. The conversion is explicit — you are telling the database exactly what type conversion you want, rather than relying on implicit conversion behaviour that may differ between databases.

The most important use cases for CAST: forcing decimal division when columns are integers — CAST(numerator AS DECIMAL) / denominator prevents integer truncation where 7/2 would return 3 instead of 3.5. Converting dates to strings for display — TO_CHAR or CAST(date_col AS VARCHAR) for concatenation with strings. Converting strings to dates when inserting or comparing with user input. Narrowing a type for a specific calculation — CAST(total_amount AS INTEGER) to drop cent precision when only whole dollars matter.

When not to use CAST: do not use CAST to work around a schema design error. If you are constantly casting a phone_number column from BIGINT to VARCHAR, fix the schema — change the column to VARCHAR. CAST is for legitimate temporary type coercion in calculations and output formatting, not a permanent workaround for wrong column types. Also avoid CAST on the column side of WHERE conditions because it prevents index usage — CAST(order_id AS VARCHAR) = '1007' disables the index on order_id. Rewrite the value side instead: order_id = CAST('1007' AS INTEGER) or simply order_id = 1007.

Cause: Type mismatch in a WHERE condition. customer_id is an INTEGER column but you compared it to a string value '1007' (with quotes). PostgreSQL does not implicitly convert between integer and text. MySQL would silently coerce the string to an integer and succeed — which is why queries written for MySQL sometimes fail on PostgreSQL.

Fix: Remove the quotes to use the correct integer literal: WHERE customer_id = 1007. If the value comes from application code that always produces strings, cast explicitly: WHERE customer_id = CAST('1007' AS INTEGER). For the long term, ensure your application passes integer values for integer columns — use typed parameters in prepared statements, not raw string interpolation.

Cause: Money is stored as FLOAT or DOUBLE instead of DECIMAL. Floating-point arithmetic cannot represent most decimal fractions exactly. Small errors (like 10.50 being stored as 10.499999...) compound across millions of rows. The SUM of one million $10.50 values stored as FLOAT may be $10,499,999.99 instead of $10,500,000.00 — a $0.01 discrepancy that causes compliance failures.

Fix: Change the column type from FLOAT to DECIMAL(precision, 2): ALTER TABLE payments ALTER COLUMN amount TYPE DECIMAL(12,2). Migrate existing data: UPDATE payments SET amount = ROUND(amount::DECIMAL, 2). Audit all financial columns across the schema and convert every FLOAT used for money. Going forward, make DECIMAL mandatory in code review for any column that stores a monetary value — reject any schema change that uses FLOAT for money.

Cause: Dates are stored as VARCHAR instead of DATE. VARCHAR sorts lexicographically — character by character. '2' comes before '15' alphabetically because '2' > '1'. The correct chronological order (Jan 2 before Jan 15) matches numeric order, not alphabetical. Any non-ISO date format stored as VARCHAR will sort incorrectly.

Fix: Change the column to DATE type: ALTER TABLE orders ALTER COLUMN order_date TYPE DATE USING TO_DATE(order_date, 'DD/MM/YYYY'). The USING clause converts existing VARCHAR values to DATE during the migration. Going forward, always insert dates as ISO 8601 strings ('2024-01-15') which the database parses correctly into DATE. If input arrives in DD/MM/YYYY format from users, convert at the application layer before inserting.

Cause: The VARCHAR(n) limit is too short for some values being inserted. The database is truncating values to fit the declared length — or in strict mode, rejecting the insert entirely. VARCHAR(20) for a product name column that needs to hold 35-character names will silently truncate in MySQL's non-strict mode, causing data loss that is invisible until a product search returns wrong names.

Fix: Increase the VARCHAR length: ALTER TABLE products ALTER COLUMN product_name TYPE VARCHAR(500). Review all VARCHAR lengths in your schema against the actual data: SELECT MAX(LENGTH(product_name)) FROM products — this shows the current maximum and helps you set a correct limit with headroom. Enable MySQL strict mode (STRICT_TRANS_TABLES) to turn silent truncation into an error so data loss is immediately visible.

Cause: Timestamps are stored as TIMESTAMP WITHOUT TIME ZONE. When the database server was in one timezone (America/Los_Angeles), values inserted at 14:30 PST were stored as 14:30 with no timezone. After migrating the server to UTC, the same values are displayed as 14:30 UTC — 8 hours ahead of the original time. All historical timestamps are now wrong relative to their actual creation time.

Fix: Going forward, use TIMESTAMPTZ for all event timestamps: ALTER TABLE events ALTER COLUMN created_at TYPE TIMESTAMPTZ USING created_at AT TIME ZONE 'America/Los_Angeles'. This converts the stored 14:30 to 22:00 UTC (the actual UTC equivalent of 14:30 PST), which displays as 14:30 when the session timezone is set to PST and as 22:00 when set to UTC — always representing the correct moment. For existing data, determine what timezone the values were originally entered in and migrate accordingly.

Write a query that shows all columns in the FreshCart 'products' table — their name, data type, whether they allow NULL, and their default value. Then write a second query that finds any product where unit_price cast to INTEGER differs from unit_price, indicating sub-dollar (cent) pricing.