Data Types in SQL

Introduction

Every column in a SQL table has a data type that determines what kind of data it can store. Choosing the right data type is crucial for:

• Data integrity: Prevent invalid data
• Storage efficiency: Use appropriate space
• Query performance: Enable optimizations
• Application logic: Match expected formats

In this lesson, we'll explore the most common PostgreSQL data types and when to use each.

Numeric Data Types

INTEGER Types

SMALLINT (2 bytes)

• Range: -32,768 to 32,767
• Use for: Small counts, ages, ratings

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    rating SMALLINT  -- 1-5 stars
);

INTEGER / INT (4 bytes)

• Range: -2,147,483,648 to 2,147,483,647
• Use for: Most whole numbers, IDs, counts

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    age INTEGER
);

BIGINT (8 bytes)

• Range: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
• Use for: Very large numbers, timestamps in milliseconds

CREATE TABLE analytics (
    page_views BIGINT
);

SERIAL, BIGSERIAL (Auto-incrementing)

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,  -- Auto-increments from 1
    total DECIMAL(10,2)
);

Decimal and Floating-Point Types

DECIMAL(precision, scale) / NUMERIC

• Exact decimal values
• Use for: Money, prices, percentages

CREATE TABLE products (
    price DECIMAL(10,2)  -- Up to 99,999,999.99
);

INSERT INTO products (price) VALUES (19.99);

REAL (4 bytes)

• 6 decimal digits precision
• Use for: Scientific calculations, approximate values

DOUBLE PRECISION (8 bytes)

• 15 decimal digits precision
• Use for: High-precision scientific data

CREATE TABLE measurements (
    latitude DOUBLE PRECISION,
    longitude DOUBLE PRECISION
);

When to use which:

• Money: Always use DECIMAL
• Scientific: REAL or DOUBLE PRECISION
• Approximate values: REAL
• Exact values: DECIMAL

Character/String Types

VARCHAR(n) (Variable length)

• Stores up to n characters
• Use for: Names, emails, addresses

CREATE TABLE users (
    name VARCHAR(100),
    email VARCHAR(255)
);

CHAR(n) (Fixed length)

• Always uses n characters (pads with spaces)
• Use for: Fixed-format codes (e.g., country codes)

CREATE TABLE countries (
    code CHAR(2)  -- US, UK, FR, etc.
);

TEXT (Unlimited length)

• No length limit
• Use for: Long content, descriptions, blog posts

CREATE TABLE posts (
    title VARCHAR(200),
    content TEXT  -- Can be very long
);

When to use which:

• Short, known length: VARCHAR(n)
• Fixed length: CHAR(n)
• Long or unknown length: TEXT

Boolean Type

BOOLEAN

• Values: TRUE, FALSE, NULL
• Use for: Flags, status indicators

CREATE TABLE tasks (
    id SERIAL PRIMARY KEY,
    description TEXT,
    completed BOOLEAN DEFAULT FALSE,
    is_public BOOLEAN
);

INSERT INTO tasks (description, completed, is_public)
VALUES ('Write SQL course', TRUE, TRUE);

SELECT * FROM tasks WHERE completed = TRUE;

Date and Time Types

DATE

• Stores year, month, day
• Format: 'YYYY-MM-DD'

CREATE TABLE events (
    event_date DATE
);

INSERT INTO events VALUES ('2024-12-25');

TIME

• Stores hour, minute, second
• Format: 'HH:MM:SS'

CREATE TABLE schedules (
    start_time TIME
);

INSERT INTO schedules VALUES ('09:30:00');

TIMESTAMP

• Stores date + time
• Format: 'YYYY-MM-DD HH:MM:SS'

CREATE TABLE logs (
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO logs (created_at) VALUES (CURRENT_TIMESTAMP);

TIMESTAMPTZ (Timestamp with time zone)

• Stores date, time, AND time zone
• Recommended for most applications

CREATE TABLE orders (
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

INTERVAL

• Stores durations
• Use for: Time differences, delays

SELECT CURRENT_TIMESTAMP + INTERVAL '7 days';  -- One week from now
SELECT CURRENT_TIMESTAMP - INTERVAL '1 hour';  -- One hour ago

JSON Types

JSON

• Stores JSON data as text
• Validates JSON format

JSONB (Binary JSON - Recommended)

• Stores JSON in binary format
• Faster queries
• Supports indexing

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    attributes JSONB
);

INSERT INTO products (name, attributes)
VALUES ('Laptop', '{
    "brand": "Dell",
    "ram": "16GB",
    "storage": "512GB SSD",
    "color": "silver"
}');

-- Query JSON data
SELECT *
FROM products
WHERE attributes->>'brand' = 'Dell';

SELECT attributes->>'ram' AS ram
FROM products;

Use cases:

• Flexible schemas
• Configuration data
• API responses
• Variable product attributes

Array Types

PostgreSQL supports arrays of any data type:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    grades INTEGER[]  -- Array of integers
);

INSERT INTO students (name, grades)
VALUES ('Alice', ARRAY[85, 90, 92, 88]);

-- Query arrays
SELECT *
FROM students
WHERE 90 = ANY(grades);  -- Has a 90

SELECT name, grades[1] AS first_grade  -- Access first element
FROM students;

UUID Type

UUID (Universally Unique Identifier)

• 128-bit unique identifier
• Use for: Distributed systems, avoiding collisions

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR(100)
);

INSERT INTO users (name) VALUES ('Alice');

SELECT * FROM users;
-- id: 550e8400-e29b-41d4-a716-446655440000 (auto-generated)

Choosing the Right Type

Size Matters

Too large:

-- Wastes space
CREATE TABLE users (
    age BIGINT  -- Overkill for ages
);

Right-sized:

CREATE TABLE users (
    age SMALLINT  -- 0-120 is enough
);

Money: Always DECIMAL

-- ❌ WRONG: Floating-point errors
CREATE TABLE orders (
    total REAL  -- Can lose cents!
);

-- ✅ CORRECT
CREATE TABLE orders (
    total DECIMAL(10,2)  -- Exact values
);

Timestamps: Include Time Zone

-- ❌ Ambiguous
CREATE TABLE events (
    created_at TIMESTAMP
);

-- ✅ Clear
CREATE TABLE events (
    created_at TIMESTAMPTZ  -- Handles time zones
);

Type Conversion (Casting)

-- Implicit conversion
SELECT '123' + 45;  -- Result: 168 (string → integer)

-- Explicit CAST
SELECT CAST('123' AS INTEGER) + 45;

-- PostgreSQL shorthand (::)
SELECT '123'::INTEGER + 45;
SELECT CURRENT_DATE::TEXT;  -- '2024-01-15'

Constraints with Types

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) CHECK (price > 0),
    stock INTEGER CHECK (stock >= 0),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

Common Patterns

Pattern 1: Audit Fields

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

Pattern 2: Soft Deletes

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    deleted_at TIMESTAMPTZ  -- NULL if active
);

Pattern 3: Flexible Metadata

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    event_type VARCHAR(50),
    metadata JSONB  -- Flexible structure
);

Practice Exercises

Create tables for the following scenarios:

Exercise 1: Blog System

Create a posts table with: id, title (max 200 chars), content (unlimited), published (boolean), published_at (timestamp).

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    published BOOLEAN DEFAULT FALSE,
    published_at TIMESTAMPTZ
);

Exercise 2: E-commerce Products

Create a products table with: id, name (100 chars), price (exact decimal), stock (integer), attributes (JSON).

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
    stock INTEGER DEFAULT 0 CHECK (stock >= 0),
    attributes JSONB
);

Key Takeaways

• ✅ INTEGER: Whole numbers (SMALLINT, INT, BIGINT)
• ✅ DECIMAL: Exact decimals (use for money)
• ✅ VARCHAR/TEXT: Strings
• ✅ BOOLEAN: True/false values
• ✅ TIMESTAMP(TZ): Dates and times
• ✅ JSONB: Flexible JSON data
• ✅ Choose appropriate sizes for efficiency
• ✅ Use constraints to enforce data rules

Next Steps

Understanding data types helps you design better databases. In the next lesson, we'll explore NULL Values—what they mean, how to handle them, and common pitfalls to avoid.