0Pricing
SQL Academy · Lesson

Data Types Overview: INT VARCHAR DATE BOOLEAN

Learn the core SQL data types: integers, varchar/text, dates and timestamps, booleans, and decimals, and how to pick the right type for each column.

Data Types Overview: INT VARCHAR DATE BOOLEAN is a free SQL Academy lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Why Types Matter

Every column has a fixed type. The type defines:

  • What values are valid
  • How much storage each row uses
  • Which operators and functions apply
  • How the planner picks indexes and joins

Integer Types

PostgreSQL has three integer sizes:

SMALLINT  -- 2 bytes,  -32768 to 32767
INTEGER   -- 4 bytes,  ±2.1 billion       (alias: INT)
BIGINT    -- 8 bytes,  ±9.2 quintillion

-- Auto-incrementing variants:
SMALLSERIAL  BIGSERIAL  SERIAL

Numeric / Decimal

For money and exact decimals, use NUMERIC(precision, scale) — never FLOAT.

CREATE TABLE invoices (
  id BIGSERIAL PRIMARY KEY,
  amount NUMERIC(12, 2) NOT NULL,   -- up to 10 digits + 2 decimals
  tax_rate NUMERIC(5, 4) NOT NULL   -- 0.0875 = 8.75%
);

Float vs Numeric

REAL (4 byte) and DOUBLE PRECISION (8 byte) are inexact — they round binary fractions. Fine for science, terrible for money.

-- Inexact:
SELECT 0.1::FLOAT + 0.2::FLOAT;   -- 0.30000000000000004

-- Exact:
SELECT 0.1::NUMERIC + 0.2::NUMERIC;  -- 0.3

Text Types: VARCHAR vs TEXT

PostgreSQL has two main text types:

-- VARCHAR(n) — variable-length, max n characters
email VARCHAR(255)

-- TEXT — variable-length, no declared limit
description TEXT

-- In PostgreSQL both have the same performance.
-- VARCHAR(n) just adds a length check.
-- CHAR(n) (fixed-length, blank-padded) — avoid.

Date and Time

Use the date/time types — never store dates as strings:

DATE         -- 2024-03-15
TIME         -- 14:30:00
TIMESTAMP    -- 2024-03-15 14:30:00      (no time zone)
TIMESTAMPTZ  -- 2024-03-15 14:30:00+02   (recommended)
INTERVAL     -- 1 day, 3 hours

-- Prefer TIMESTAMPTZ unless you have a strong reason not to.

Boolean

BOOLEAN stores TRUE, FALSE or NULL.

CREATE TABLE tasks (
  id BIGSERIAL PRIMARY KEY,
  done BOOLEAN NOT NULL DEFAULT false
);

SELECT * FROM tasks WHERE done;        -- shorthand for done = true
SELECT * FROM tasks WHERE NOT done;

UUID

For globally unique identifiers:

CREATE TABLE api_keys (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id BIGINT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

JSON and JSONB

For semi-structured documents:

-- JSON   — text, preserves whitespace, no index
-- JSONB  — binary, deduplicated keys, indexable with GIN

CREATE TABLE events (
  id BIGSERIAL PRIMARY KEY,
  payload JSONB NOT NULL
);

Arrays

PostgreSQL columns can be arrays of any base type:

CREATE TABLE posts (
  id BIGSERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  tags TEXT[] NOT NULL DEFAULT '{}'
);

INSERT INTO posts (title, tags) VALUES
  ('Hello', ARRAY['sql', 'intro']);

SELECT * FROM posts WHERE 'sql' = ANY(tags);

Choosing the Right Type

Rules of thumb:

  • Money → NUMERIC (never FLOAT)
  • IDs → BIGSERIAL or UUID
  • Dates → TIMESTAMPTZ for events, DATE for calendar days
  • Short codes → VARCHAR(n); long text → TEXT
  • Flags → BOOLEAN

Recap

Pick the narrowest type that fits.

  • Right types = smaller indexes, better stats, fewer bugs
  • NUMERIC for exact decimals
  • TIMESTAMPTZ for event timestamps
  • Avoid storing typed data as strings — you lose validation and indexing

Quick Check

You need to store a product price. Which type should you pick?

Frequently asked questions

Is the “Data Types Overview: INT VARCHAR DATE BOOLEAN” lesson free?

Yes — the full text of “Data Types Overview: INT VARCHAR DATE BOOLEAN” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Data Types Overview: INT VARCHAR DATE BOOLEAN”?

Learn the core SQL data types: integers, varchar/text, dates and timestamps, booleans, and decimals, and how to pick the right type for each column. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Data Types Overview: INT VARCHAR DATE BOOLEAN” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. What an RDBMS Is and Why Tables Matter
  2. Primary Keys and Uniqueness
  3. NULL: The Third Truth Value
  4. Data Types Overview: INT VARCHAR DATE BOOLEAN
← Back to SQL Academy