Topic 3 of 52
Table Columns Schema Datatypes
Overview
Every column in a SQL table must have a defined data type that constrains what values can be stored. Choosing the right data type saves storage, improves performance, and prevents invalid data from entering the database.
Syntax
sql
-- Numeric types
INT / INTEGER -- whole numbers (-2B to 2B)
BIGINT -- large integers
DECIMAL(10, 2) -- exact decimal (use for money!)
FLOAT / REAL -- approximate decimal (NOT for money)
-- String types
VARCHAR(255) -- variable-length string (most common)
CHAR(10) -- fixed-length string
TEXT -- unlimited length text
-- Date/Time types
DATE -- '2025-06-13'
TIMESTAMP -- '2025-06-13 10:30:00'
TIMESTAMPTZ -- with timezone (PostgreSQL)
TIME -- '10:30:00'
-- Boolean
BOOLEAN / BOOL -- true / false
-- Special
UUID -- universal unique identifier
JSON / JSONB -- JSON data (JSONB is indexed in PostgreSQL)
ARRAY -- array of values (PostgreSQL)Common Pitfalls
- NEVER use FLOAT for money — use DECIMAL(10,2) to avoid floating-point rounding errors.
- VARCHAR(255) is a common default, but don't blindly use it — think about actual max length (email: 254 chars, phone: 20).
- Interview tip: UUID vs SERIAL — UUID is globally unique across systems (good for distributed apps); SERIAL is simpler but sequential (guessable).
Real-World Example
A well-typed users table for a SaaS application:
example
sql
CREATE TABLE users (
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
full_name VARCHAR(100) NOT NULL,
age SMALLINT CHECK (age >= 13 AND age <= 120),
bio TEXT,
plan VARCHAR(20) DEFAULT 'free' CHECK (plan IN ('free','pro','enterprise')),
monthly_bill DECIMAL(10,2) DEFAULT 0.00,
is_verified BOOLEAN DEFAULT FALSE,
preferences JSONB DEFAULT '{}',
created_at TIMESTAMPTZ DEFAULT NOW(),
last_login TIMESTAMPTZ
);