Module 1, Session 4. Why TEXT-for-everything is dangerous, core PostgreSQL data types, the type decision tree, and converting TEXT columns with ALTER TABLE ... TYPE ... USING.
Open in Present mode · Live class schedule · Try the playground
Jo class mein bola, yahan nukte mein likha hai.
What I said in class, written here as key points.
Why TEXT-for-everything is a production time bomb, mastering the 8 core types, the 10-second decision tree, and in-place schema conversions with ALTER TABLE ... TYPE ... USING.
Four architectural pillars to choose the right data type for every column and eliminate silent data corruption
TEXT is a time bomb. PostgreSQL data types are hardware-level gatekeepers that reject corrupted writes, save massive SSD memory, and guarantee exact financial arithmetic.In the previous session, we moved from theory into writing real SQL in PostgreSQL:
CREATE SCHEMA and CREATE TABLE to structure your database world.
INSERT INTO and verified them with SELECT *.
TEXT.
Using TEXT for everything was training wheels. In production, it creates severe bugs:
TEXT price column happily accepts 'free', '-500', or 'N/A'.
SUM(amount) or AVG(rating) fails immediately on TEXT columns.
TEXT columns using ALTER TABLE.
TEXT made table prototyping fast, in production every column requires an explicit type. Types are hardware-level constraints that prevent corrupted writes and enable aggregate calculations.'Rs.15,000' and 'fifteen thousand' as payment amounts. TEXT accepted every single one — no error, no warning.
-- Frontend bug: TEXT accepted all of these INSERT INTO payments (user_id, amount) VALUES ('1003', 'Rs.15,000'); -- ✓ stored INSERT INTO payments (user_id, amount) VALUES ('1004', 'fifteen thousand'); -- ✓ stored -- Quarter-end SUM blows up: SELECT SUM(amount::NUMERIC) FROM payments; -- ERROR: invalid input syntax for type -- numeric: 'Rs.15,000' -- The fix — one ALTER, should have been day 0: ALTER TABLE payments ALTER COLUMN amount TYPE NUMERIC(12,2) USING amount::numeric;
Every vulnerability in the fintech incident directly maps to an essential PostgreSQL data type that rejects corrupted data at the hardware gate:
| Fintech Bug Vector | Protective Data Type | Production Defense Guarantee |
|---|---|---|
💵 Payment Amounts ('Rs.15,000') |
NUMERIC(12, 2) | Exact base-10 arithmetic, rejects corrupt text at write time, zero float drift. |
📱 Phone Numbers ('09876543210') |
VARCHAR(15) | Preserves leading zeros and '+' formatting, prevents erroneous math. |
| ⚡ UPI Transaction IDs | BIGINT | 64-bit scale (19-digit numbers) — never overflows even at 10,000 txns/sec. |
| 📅 Payment Timestamps | TIMESTAMPTZ | Microsecond precision stored in UTC — immune to daylight saving shifts. |
| 🔘 Settlement Status Flags | BOOLEAN | Strict 1-byte binary state (TRUE / FALSE) — zero ambiguity. |
In database hardware, data types are physical byte-sized containers on SSD & RAM. Pick the container that comfortably holds your data without wasting storage — but never let it overflow.
ERROR: smallint out of range
INT without second thought.
INTEGER (max 2147483647, about 214 crore views). “Gangnam Style” was the first video to get close, so YouTube moved the counter to 64-bit BIGINT months before it reached the ceiling. One more view on a full INT would have wrapped to a negative number. (The spinning counter people saw was a joke animation.)
SERIAL is simply a 4-byte INT column pre-wired to a mechanical numbering counter:
PostgreSQL gives you four integer sizes plus auto-incrementing serials. Pick the smallest type that will NEVER overflow.
rating = 5 • age = 28 • seat = 42order_id = 104291 • cust_id = 4821upi_txn = 920194829104 • views = 520 croreid SERIAL PRIMARY KEY → 1, 2, 3...-- 1. Create a table with properly sized integer columns: CREATE TABLE warehouse_inventory ( item_id SERIAL PRIMARY KEY, -- Auto-increments: 1, 2, 3... shelf_tier SMALLINT NOT NULL, -- Tier 1 to 5 (Saves 50% memory) stock_count INT NOT NULL, -- Up to 214 Crore units global_rfid BIGINT NOT NULL -- 64-bit global tracking barcode ); -- 2. Verify the SERIAL sequence engine underneath: -- PostgreSQL creates a sequence: warehouse_inventory_item_id_seq -- Sets default to: nextval('warehouse_inventory_item_id_seq') -- 3. Overflow demonstration (Safe failure): INSERT INTO warehouse_inventory (shelf_tier, stock_count, global_rfid) VALUES (99999, 100, 982104829104); -- ERROR: smallint out of range (-32768 to 32767) -- 4. Each type's exact ceiling (this is what ▶ Run shows): SELECT 32767::SMALLINT AS smallint_max, 2147483647::INT AS int_max, 9223372036854775807::BIGINT AS bigint_max;
Binary computers cannot represent base-10 fractions like 0.1 accurately. Storing money as FLOAT causes silent, accumulating loss across bank ledgers.
1/10 as exact finite binaries (identical to how 1/3 = 0.3333... has infinite decimals in base-10). It creates tiny phantom discrepancies on every single write.
Storing currency as binary floating-point (REAL/FLOAT) introduces silent rounding drift. Always use NUMERIC for money.
price = 1499.50 • gst = 269.91latitude = 18.5204 • temp = 36.6orbital_radius = 1.496e+8-- 1. Proof of Float Drift vs Exact Numeric in PostgreSQL: SELECT 0.1::FLOAT8 + 0.2::FLOAT8 AS float_drift, 0.1::NUMERIC + 0.2::NUMERIC AS exact_money; -- float_drift: 0.30000000000000004 (❌ binary approximation, not 0.3) -- exact_money: 0.3 (✓ EXACT: matches rupee & paise arithmetic) -- 2. Production Table Schema with Financial Constraints: CREATE TABLE customer_orders ( order_id SERIAL PRIMARY KEY, subtotal NUMERIC(10, 2) NOT NULL, -- Up to ₹9,99,99,999.99 with exact paise gst_tax NUMERIC(10, 2) NOT NULL, -- 18% GST calculation total_billed NUMERIC(10, 2) GENERATED ALWAYS AS (subtotal + gst_tax) STORED );
How text is physically packed on disk, why CHAR wastes blocks, and why formatted numeric identifiers are NOT numbers.
'Pune' consumes 10 full bytes of SSD storage.
CHAR(n) in modern PostgreSQL schemas.
'Pune' consumes only 4 bytes of actual character data.
TEXT and VARCHAR share identical performance. The (n) in VARCHAR is strictly a validation guard.
9876543210 (CORRUPTED!)"+91 09876 543210" (✓ SAFE)
SUM(), AVG(), or multiply it? If NO — it is TEXT (VARCHAR), NOT a number!
PostgreSQL stores all three with identical performance under the hood. Choose by data constraint, not speed myth.
country_code = 'IN' (CHAR(2))email = '[email protected]' (VARCHAR(255))notes, reviews, bios, descriptions-- 1. Inspecting Actual Storage Footprint with octet_length(): SELECT octet_length('Pune'::CHAR(10)) AS char_bytes, -- 10 bytes (Padded with 6 spaces!) octet_length('Pune'::VARCHAR(10)) AS varchar_bytes, -- 4 bytes (Zero wasted bytes!) octet_length('Pune'::TEXT) AS text_bytes; -- 4 bytes (Same speed & storage!) -- 2. The Phone Number Trap Proof: CREATE TEMP TABLE contacts (phone_int BIGINT, phone_txt VARCHAR(15)); INSERT INTO contacts VALUES (09876543210, '09876543210'); SELECT phone_int, phone_txt FROM contacts; -- phone_int: 9876543210 (❌ Leading 0 stripped! Corrupt phone number!) -- phone_txt: 09876543210 (✓ Preserved intact!)
Calendar dates, instant timestamps with timezone shifts, durations, and binary decision flags.
birthday • order_date • hiring_date
now() - INTERVAL '7 days'.
subscription_expires_at • trial_end
TRUE, FALSE, or NULL (unknown). Used for flags and status checks.
is_active • is_delivered • verified
Store moments in time accurately across timezones, and binary states with boolean flags.
date_of_birth = '1998-05-16'opening_time = '09:30:00'created_at = '2026-10-11 14:30:00+05:30'is_active = TRUE • verified = FALSE-- 1. TIMESTAMPTZ Automatic Timezone Normalization: SET timezone = 'Asia/Kolkata'; SELECT now() AS kolkata_time; -- e.g. 2026-10-11 14:30:00+05:30 SET timezone = 'America/New_York'; SELECT now() AS new_york_time; -- e.g. 2026-10-11 05:00:00-04:00 (Same instant in history!) -- 2. Practical Date & Interval Math: SELECT CURRENT_DATE + INTERVAL '30 days' AS subscription_expiry, now() - INTERVAL '7 days' AS active_user_window, AGE(CURRENT_DATE, '1998-05-16') AS exact_customer_age;
Follow this 4-step checklist for every column you create. Never guess.
INT: customer_id • BIGINT: txn_idprice = 1499.50 • tax = 18.00phone = '+91-98765-43210' • review = TEXTcreated_at = now() • dob = '1998-05-16'INT, BIGINT, or NUMERIC. If it has leading zeros or symbols → VARCHAR. If it tracks a moment in history → TIMESTAMPTZ. If it is binary state → BOOLEAN.
Convert columns to proper types WITHOUT dropping tables — using ALTER TABLE ... ALTER COLUMN ... TYPE ... USING. The USING clause tells PostgreSQL how to cast existing text values.
ALTER TABLE college.students ALTER COLUMN student_id TYPE INT USING student_id::int; -- ▶ Run tries the cast alone: SELECT '104291'::int AS student_id;
'N/A') exist. Zero table corruption.
ALTER TABLE college.students ALTER COLUMN date_of_birth TYPE DATE USING date_of_birth::date; -- ▶ Run tries the cast alone: SELECT '1998-05-16'::date AS date_of_birth;
AGE(CURRENT_DATE, date_of_birth).
ALTER TABLE college.fee_payments ALTER COLUMN amount TYPE NUMERIC(10,2) USING amount::numeric; -- ▶ Run tries the cast alone: SELECT '1499.50'::numeric(10,2) AS amount;
ALTER TABLE college.students ALTER COLUMN is_active TYPE BOOLEAN USING is_active::boolean; -- ▶ Run tries the cast alone: SELECT 'true'::boolean AS is_active, '1'::boolean AS flag_one;
'true', 'false', 't', 'f', '1', '0' automatically.
ALTER TABLE t ADD COLUMN created_at TIMESTAMPTZ DEFAULT now();
ALTER TABLE t RENAME COLUMN old_name TO new_name;
ALTER TABLE t DROP COLUMN unused;
All 9 columns of the real RetailMart orders table (1,50,000 orders) — each with its proper type. None of them left as generic TEXT.
Choosing the RIGHT type is the core skill — every wrong type is a future bug in production.
Click any question below to inspect the verified technical model answer, the core underlying engineering concept, and the exact trap interviewers listen for.
FLOAT and REAL are binary floating-point approximations — 0.1 + 0.2 becomes 0.30000000000000004. Over lakhs of transactions, accumulated float drift causes serious financial discrepancies. Always use NUMERIC(precision, scale) for monetary amounts because it stores exact base-10 decimals with zero rounding loss.
Close your notes. Answer from memory. Click any card to reveal the verified answer.
The core principles governing PostgreSQL data integrity and storage types.
NUMERIC for money (never FLOAT), VARCHAR for formatted codes, DATE for calendar days, and TIMESTAMPTZ for moments. Types reject invalid data at write time, before it can corrupt downstream queries.
ALTER TABLE ... ALTER COLUMN ... TYPE ... USING converts existing data without dropping tables. If a cast fails, bad data is hiding in your table — clean it first.
Phone numbers, PIN codes, Aadhaar, and tracking IDs contain numbers but are not quantitative. If you don't SUM() or AVG() it, store it as VARCHAR.
SMALLINT, INT, BIGINT, NUMERIC(p,s), VARCHAR(n), TEXT, TIMESTAMPTZ, and BOOLEAN cover 95% of real-world database design needs.
A practical reference for core PostgreSQL data types and everyday ALTER migrations.
Complete these hands-on migration drills in your local database before moving forward to Constraints.
Run a query on information_schema.columns to inspect your tables from Session 3 and list all columns currently defined as text.
Use ALTER TABLE ... TYPE INT USING col::int on your primary IDs and whole numbers.
Migrate calendar date columns to DATE using USING date_col::date.
Convert fee and price columns to NUMERIC(10,2). Prove that SUM(fee) now works directly without casting!