Cheatsheet for Postgres-specific scalar types that aren’t JSONB/array.

Range types

from sqlalchemy.dialects.postgresql import (
    INT4RANGE, INT8RANGE, NUMRANGE,
    DATERANGE, TSRANGE, TSTZRANGE,
    Range,
)

class Booking(Base):
    __tablename__ = "bookings"
    id: Mapped[int] = mapped_column(primary_key=True)
    room_id: Mapped[int]
    period: Mapped[Range] = mapped_column(TSTZRANGE)

Postgres ranges are [low, high) by default (inclusive low, exclusive high).

Construct ranges

from datetime import datetime
from sqlalchemy.dialects.postgresql import Range

period = Range(datetime(2026, 5, 1), datetime(2026, 5, 8), bounds="[)")
booking.period = period

Query operators

from sqlalchemy import text

# Overlaps (&&)
stmt = select(Booking).where(Booking.period.op("&&")(other_period))

# Contains element (@>)
stmt = select(Booking).where(Booking.period.contains(some_dt))

# Contained by (<@)
stmt = select(Booking).where(text("period <@ :p"), {"p": Range(...)})

# Lower / upper bound
stmt = select(Booking).where(func.lower(Booking.period) > t1)
stmt = select(Booking).where(func.upper(Booking.period) < t2)

Exclusion constraint (no overlap)

from sqlalchemy import ExcludeConstraint

class Booking(Base):
    __tablename__ = "bookings"
    id: Mapped[int] = mapped_column(primary_key=True)
    room_id: Mapped[int]
    period: Mapped[Range] = mapped_column(TSTZRANGE)
    
    __table_args__ = (
        ExcludeConstraint(("room_id", "="), ("period", "&&"), name="ex_no_overlap"),
    )

DB-enforced: same room can’t have overlapping bookings.

INTERVAL

from datetime import timedelta
from sqlalchemy import Interval

class Subscription(Base):
    duration: Mapped[timedelta] = mapped_column(Interval)

Postgres INTERVAL ↔ Python timedelta.

stmt = select(Subscription).where(Subscription.duration > timedelta(days=30))

INTERVAL arithmetic

from sqlalchemy import func, text

# Add interval to timestamp
stmt = select(Event).where(Event.starts_at + text("interval '1 hour'") > now)
stmt = select(Event).where(Event.ends_at - Event.starts_at > timedelta(hours=2))

citext (case-insensitive)

from sqlalchemy.dialects.postgresql import CITEXT

class User(Base):
    email: Mapped[str] = mapped_column(CITEXT, unique=True)

'[email protected]' == '[email protected]' evaluates true.

Requires CREATE EXTENSION citext.

INET / CIDR

from sqlalchemy.dialects.postgresql import INET, CIDR

class Visitor(Base):
    ip: Mapped[str] = mapped_column(INET)
    subnet: Mapped[str | None] = mapped_column(CIDR)

INET queries

from sqlalchemy import text

# Subnet contains IP
stmt = select(Visitor).where(text("ip <<= :net"), {"net": "10.0.0.0/8"})

# Network family (4 or 6)
stmt = select(Visitor).where(text("family(ip) = 4"))

# Network address
stmt = select(func.host(Visitor.ip))

MAC addresses

from sqlalchemy.dialects.postgresql import MACADDR

class Device(Base):
    mac: Mapped[str] = mapped_column(MACADDR)

money (avoid)

Postgres has money type but it’s locale-dependent. Prefer NUMERIC(12, 2) for storage; format in the app.

bytea (binary)

from sqlalchemy import LargeBinary

class Doc(Base):
    content: Mapped[bytes] = mapped_column(LargeBinary)

For large binary: consider object storage (S3) + URL column.

UUID

import uuid
from sqlalchemy.dialects.postgresql import UUID
from sqlalchemy import text

class Doc(Base):
    id: Mapped[uuid.UUID] = mapped_column(
        UUID(as_uuid=True),
        primary_key=True,
        server_default=text("gen_random_uuid()"),
    )

PG 13+: gen_random_uuid() built-in.

For UUIDv7 (sortable; great for primary keys with time ordering): use the uuid-osp extension or generate in Python.

ENUM

from sqlalchemy import Enum as SAEnum
import enum

class Role(str, enum.Enum):
    USER = "user"; ADMIN = "admin"

class User(Base):
    role: Mapped[Role] = mapped_column(SAEnum(Role, name="user_role", create_type=True))

Adding a value: ALTER TYPE user_role ADD VALUE 'editor' (PG 12+ supports in transaction).

For schema flexibility, VARCHAR + CHECK is easier.

tsvector (full-text)

from sqlalchemy.dialects.postgresql import TSVECTOR

class Post(Base):
    title: Mapped[str]
    body: Mapped[str]
    search: Mapped[str] = mapped_column(
        TSVECTOR,
        Computed("to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''))", persisted=True),
    )

Covered in detail in Cheatsheet 06 — FTS .

hstore (legacy; prefer JSONB)

from sqlalchemy.dialects.postgresql import HSTORE

class Doc(Base):
    meta_: Mapped[dict] = mapped_column(HSTORE)

Older flat key-value type. JSONB superseded.

Common mistakes

  • Bound syntax confusion [) vs [] vs ().
  • Forgetting EXCLUDE requires a GIST index over the range column.
  • Using citext on indexed PK column — sometimes interacts with cluster ordering.
  • INTERVAL precision — sub-second sometimes lost across drivers.

Read this next

If you want my booking-with-no-overlap reference, it’s at rajpoot.dev .

Building something AI-, backend-, or data-heavy and want a second pair of eyes? I do consulting and freelance work — see my projects and ways to reach me at rajpoot.dev .