Modern examples of diverse examples of SQL data types

Before definitions, let’s start with how developers actually use these types. Here are some realistic scenarios that give you examples of diverse examples of SQL data types in modern systems.

A payments platform might store:

• Monetary amounts in DECIMAL(19,4) to avoid floating‑point rounding errors.
• Transaction timestamps in TIMESTAMPTZ (timestamp with time zone) to correctly handle users in New York and London.
• Fraud signals in a JSONB column to flexibly store variable attributes.

An analytics warehouse could use:

• BIGINT for event IDs and counters that can reach billions.
• DATE for partitioning daily data.
• BOOLEAN flags like is_bot or is_test_event for filtering.

An IoT platform might rely on:

• FLOAT or DOUBLE PRECISION for sensor readings.
• GEOGRAPHY or GEOMETRY for device locations.
• ARRAY types for batches of measurements.

Those are just a few examples of diverse examples of SQL data types being used with intent, not guesswork.

Numeric data types: best examples from finance, analytics, and IoT

When people ask for an example of SQL numeric types, they usually mean “which type should I use for money, counts, and measurements?” The answer depends heavily on how the data will be used.

Integers for IDs and counts

In most relational databases (PostgreSQL, MySQL, SQL Server), you’ll see families of integer types: SMALLINT, INT / INTEGER, and BIGINT.

A typical table for an e‑commerce order system might look like this:

CREATE TABLE orders (
    order_id      BIGINT GENERATED ALWAYS AS IDENTITY,
    customer_id   BIGINT NOT NULL,
    item_count    INT    NOT NULL,

    -- other columns...
    PRIMARY KEY (order_id)
);

BIGINT is the workhorse in 2024–2025 for primary keys and high‑volume event data. Modern systems can easily generate billions of rows, and using INT can paint you into a corner when it overflows.

Real examples include:

• Event tracking tables in analytics platforms storing trillions of events.
• Ad impressions and clicks where counters grow quickly.
• Social networks tracking follower counts that can exceed INT ranges.

Fixed‑point decimals for money

If you’re dealing with currency, DECIMAL(p, s) or NUMERIC(p, s) is almost always the right choice. Floating‑point types introduce subtle rounding errors that are unacceptable in finance.

CREATE TABLE payments (
    payment_id    BIGINT GENERATED BY DEFAULT AS IDENTITY,
    amount        DECIMAL(19, 4) NOT NULL,
    currency_code CHAR(3)        NOT NULL,
    created_at    TIMESTAMPTZ    NOT NULL DEFAULT NOW(),
    PRIMARY KEY (payment_id)
);

Here, DECIMAL(19,4) is a common pattern in real examples from banking and fintech. It can represent very large amounts with four decimal places, enough for most currencies and fee calculations.

Floating‑point for scientific and sensor data

For scientific workloads or IoT scenarios, you often need REAL / FLOAT4 or DOUBLE PRECISION / FLOAT8.

CREATE TABLE sensor_readings (
    device_id     BIGINT      NOT NULL,
    reading_time  TIMESTAMPTZ NOT NULL,
    temperature_c DOUBLE PRECISION NOT NULL,
    humidity_pct  REAL,
    PRIMARY KEY (device_id, reading_time)
);

Here, small rounding errors are acceptable, but the ability to store a wide range of values and perform fast calculations is more important.

Text and string types: examples include emails, logs, and JSON keys

String handling is where a lot of schemas go wrong. Developers often default to VARCHAR(255) everywhere, which is lazy and can backfire.

Short, constrained strings

For fixed‑format fields like ISO country codes or three‑letter currency codes, CHAR(n) or short VARCHAR types make intent clear:

CREATE TABLE countries (
    country_code CHAR(2)  PRIMARY KEY,
    name         VARCHAR(100) NOT NULL
);

This gives a concrete example of using a fixed‑length type where the domain is well‑defined.

Emails, usernames, and URLs

Modern apps frequently store user identifiers and URLs. These are unpredictable in length, so VARCHAR without an arbitrary low limit or TEXT is safer:

CREATE TABLE users (
    user_id    BIGINT GENERATED ALWAYS AS IDENTITY,
    email      VARCHAR(320) UNIQUE NOT NULL,
    username   VARCHAR(50)  UNIQUE NOT NULL,
    profile_url TEXT,
    PRIMARY KEY (user_id)
);

The 320 length for email follows IETF recommendations on maximum email address length. This is a nice example of tying SQL data types to external standards instead of guessing.

Large text: logs and documents

For logs, descriptions, and unstructured content, TEXT (or CLOB in some systems) is the usual choice.

CREATE TABLE error_logs (
    log_id      BIGINT GENERATED ALWAYS AS IDENTITY,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    level       VARCHAR(10) NOT NULL,
    message     TEXT        NOT NULL,
    metadata    JSONB,
    PRIMARY KEY (log_id)
);

Here you see examples of diverse examples of SQL data types working together: TEXT for the message and JSONB for structured metadata.

Date and time: real examples with time zones, intervals, and analytics

Time is where SQL data types get interesting, especially with global users and daylight saving time.

Timestamp with time zone for user activity

For any user‑facing event, TIMESTAMPTZ (or datetimeoffset in SQL Server) is the safer default. It stores an absolute point in time.

CREATE TABLE user_sessions (
    session_id    BIGINT GENERATED ALWAYS AS IDENTITY,
    user_id       BIGINT      NOT NULL,
    started_at    TIMESTAMPTZ NOT NULL,
    ended_at      TIMESTAMPTZ,
    user_agent    TEXT,
    PRIMARY KEY (session_id)
);

This supports analytics across time zones without guessing which offset applied at the time of the event.

Date for reporting and partitions

For daily reporting and table partitioning, DATE is simpler and more storage‑efficient.

CREATE TABLE daily_metrics (
    metric_date DATE      NOT NULL,
    site_id     BIGINT    NOT NULL,
    visits      BIGINT    NOT NULL,
    signups     BIGINT    NOT NULL,
    revenue_usd DECIMAL(19, 4) NOT NULL,
    PRIMARY KEY (metric_date, site_id)
);

This is a classic example of using DATE instead of TIMESTAMP when you don’t care about the time of day.

Intervals for durations and SLAs

INTERVAL types are underused but powerful for expressing durations.

CREATE TABLE service_level_agreements (
    sla_id         BIGINT GENERATED ALWAYS AS IDENTITY,
    name           VARCHAR(100) NOT NULL,
    response_time  INTERVAL     NOT NULL,
    resolution_time INTERVAL    NOT NULL,
    PRIMARY KEY (sla_id)
);

Now you can compare ticket.created_at to ticket.resolved_at and check if it’s within the allowed INTERVAL without awkward arithmetic.

For more on handling time data correctly, the NIST time services provide background on time standards that influence database design.

Boolean, enums, and constrained values: clean modeling examples

Sometimes you only need yes/no or a small set of allowed values. This is where BOOLEAN and enum‑like patterns shine.

Simple boolean flags

BOOLEAN is great for clear, binary states.

CREATE TABLE feature_flags (
    flag_key    VARCHAR(100) PRIMARY KEY,
    is_enabled  BOOLEAN      NOT NULL DEFAULT FALSE,
    description TEXT
);

This is one of the best examples of a type that improves readability. is_enabled = TRUE is much clearer than status = 1.

Enums and lookup tables

Some databases support native ENUM types. Others rely on lookup tables.

CREATE TABLE order_statuses (
    status_code VARCHAR(20) PRIMARY KEY,
    description VARCHAR(100) NOT NULL
);

CREATE TABLE customer_orders (
    order_id     BIGINT GENERATED ALWAYS AS IDENTITY,
    status_code  VARCHAR(20) NOT NULL REFERENCES order_statuses(status_code),
    created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (order_id)
);

This pattern gives you a real example of using string types plus foreign keys to simulate enums while keeping flexibility.

JSON, arrays, and semi‑structured data: modern 2024–2025 patterns

Modern SQL databases increasingly support semi‑structured data, blurring the lines with NoSQL. In 2024–2025, JSON/JSONB and ARRAY types are everywhere.

JSON for flexible metadata

A product catalog is a classic use case:

CREATE TABLE products (
    product_id   BIGINT GENERATED ALWAYS AS IDENTITY,
    name         VARCHAR(200) NOT NULL,
    base_price   DECIMAL(19, 4) NOT NULL,
    attributes   JSONB,  -- color, size, tags, etc.
    PRIMARY KEY (product_id)
);

Here, attributes might contain different keys for clothing vs. electronics. This is a practical example of diverse examples of SQL data types coexisting: structured columns for core fields, JSON for flexible extras.

Arrays for tags and multi‑valued attributes

Some workloads benefit from ARRAY types. For example, storing multiple tags:

CREATE TABLE articles (
    article_id BIGINT GENERATED ALWAYS AS IDENTITY,
    title      VARCHAR(200) NOT NULL,
    tags       TEXT[]       NOT NULL,
    published_at TIMESTAMPTZ,
    PRIMARY KEY (article_id)
);

This lets you query tags with array operators instead of maintaining a separate join table in smaller systems.

When you’re deciding between JSON, arrays, and normalized tables, it helps to think about query patterns. If you need to index individual keys or values heavily, JSON indexes and properly normalized schemas usually win.

Spatial and specialized types: geolocation, ranges, and more

As applications get more location‑aware and data‑rich, the best examples of SQL data types go beyond the basics.

Geospatial types for maps and distance queries

PostgreSQL with PostGIS adds GEOMETRY and GEOGRAPHY types. A ride‑sharing or delivery app might use:

CREATE TABLE driver_locations (
    driver_id    BIGINT PRIMARY KEY,
    location     GEOGRAPHY(POINT, 4326) NOT NULL,
    updated_at   TIMESTAMPTZ           NOT NULL
);

Now you can run queries like “find all drivers within 2 miles of this point” directly in SQL. This is a concrete example of diverse examples of SQL data types enabling features that would be painful to implement manually.

For background on geographic coordinate systems and mapping, the USGS provides solid reference material.

Range types for periods and numeric spans

PostgreSQL’s range types (int4range, numrange, tsrange, tstzrange, etc.) let you store intervals directly.

CREATE TABLE room_bookings (
    booking_id   BIGINT GENERATED ALWAYS AS IDENTITY,
    room_id      BIGINT      NOT NULL,
    booked_during TSRANGE    NOT NULL,
    user_id      BIGINT      NOT NULL,
    PRIMARY KEY (booking_id)
);
``;

You can then ask whether two bookings overlap using range operators. This is a strong example of how SQL data types can express business rules directly in the schema.

### UUIDs and binary types

For distributed systems, `UUID` is common for identifiers, and `BYTEA` / `VARBINARY` for binary blobs.

```sql
CREATE TABLE api_keys (
    key_id      UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id     BIGINT NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    revoked_at  TIMESTAMPTZ
);

This pattern shows up in microservices and public APIs where exposing sequential numeric IDs is undesirable.

Putting it together: examples of diverse examples of SQL data types in one schema

To see how this all fits, here’s a trimmed‑down schema for a modern subscription SaaS product that combines many of the best examples we’ve discussed.

CREATE TABLE customers (
    customer_id   BIGINT GENERATED ALWAYS AS IDENTITY,
    email         VARCHAR(320) UNIQUE NOT NULL,
    full_name     VARCHAR(200) NOT NULL,
    country_code  CHAR(2)      NOT NULL,
    created_at    TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    metadata      JSONB,
    PRIMARY KEY (customer_id)
);

CREATE TABLE subscriptions (
    subscription_id BIGINT GENERATED ALWAYS AS IDENTITY,
    customer_id     BIGINT      NOT NULL REFERENCES customers(customer_id),
    plan_code       VARCHAR(50) NOT NULL,
    price_usd       DECIMAL(19, 4) NOT NULL,
    active          BOOLEAN     NOT NULL DEFAULT TRUE,
    billing_period  INTERVAL    NOT NULL,
    current_term    TSTZRANGE   NOT NULL,
    PRIMARY KEY (subscription_id)
);

CREATE TABLE usage_events (
    event_id     BIGINT GENERATED ALWAYS AS IDENTITY,
    customer_id  BIGINT      NOT NULL REFERENCES customers(customer_id),
    event_time   TIMESTAMPTZ NOT NULL,
    event_type   VARCHAR(50) NOT NULL,
    properties   JSONB,
    PRIMARY KEY (event_id)
);

Here you get:

• Numeric types (BIGINT, DECIMAL) for identifiers and prices.
• Text types (VARCHAR, JSONB) for flexible attributes.
• Date/time (TIMESTAMPTZ, INTERVAL, TSTZRANGE) for billing logic.
• Boolean flags for active/inactive status.

This is a realistic, modern example of diverse examples of SQL data types working together to support both transactional and analytical queries.

For further reading on data modeling patterns and trade‑offs, many university database courses publish open materials; for example, MIT’s database course notes at mit.edu remain surprisingly relevant even as new data types appear.

FAQ: common questions about examples of SQL data types

Q: What are good examples of SQL data types to use for money and pricing?
For money, use fixed‑point types like DECIMAL(19,4) or NUMERIC(19,4). These avoid the rounding issues of FLOAT and are common in real examples from banking and e‑commerce platforms.

Q: Can you give an example of when to use TIMESTAMPTZ instead of TIMESTAMP?
Use TIMESTAMPTZ when events involve users in multiple time zones or when daylight saving time matters. Logging user logins, payments, and API calls are classic examples of diverse examples of SQL data types where TIMESTAMPTZ is the safer choice.

Q: What are examples of using JSON in SQL databases?
Typical examples include storing product attributes, user preferences, or variable event properties. A column like attributes JSONB in a products table lets you handle different fields for different product categories without schema changes.

Q: Is VARCHAR(255) always a safe default?
Not really. It’s a habit from older systems, not a rule. Better examples include using CHAR(2) for country codes, VARCHAR(320) for emails based on standards, and TEXT for long content. Pick lengths and types that match how the data is actually used.

Q: What’s an example of a bad data type choice that causes problems later?
Using INT for an ID in a high‑volume table that eventually exceeds its maximum value, or using FLOAT for currency and discovering rounding errors in financial reports. Both are real examples from production systems that ended up needing painful migrations.

If you treat schemas as long‑term contracts rather than quick guesses, you’ll naturally look for better, more realistic examples of diverse examples of SQL data types before you commit.