Postgres data types, the 2026 subset

Last lesson you wrote uuid(), text(), integer(), and timestamp() into your first table, and you took every one of them on faith. You never asked why that type was the right one. This lesson answers that question.

Postgres ships around forty built-in types, and that number leads people to think they need to learn all forty. You don’t. A 2026 SaaS app reaches for about eight of them, day in and day out. The skill isn’t memorizing the catalogue; it’s knowing those eight defaults cold, and knowing the one tempting but wrong type that shadows each of them. Almost every “we stored money as a float and the books don’t balance” incident traces back to picking the type that looked right.

That frames the whole lesson. A SaaS app has many kinds of column: a name, a price, a timestamp, an ID, a status, a flexible payload, a list of tags, an IP address. For each one you’ll learn the correct Postgres type, the Drizzle builder that maps to it, and the trap in the obvious alternative. We keep building the same domain from last lesson, organizations and invoices, and add the columns a real invoice needs: an amount, a status, dates, a tag list, a webhook payload.

One promise up front, so you know where this is heading: the whole lesson condenses to a single lookup table at the end, with the kind of column on the left and the builder you reach for on the right. That table is what you’ll keep. Everything between here and there is the reasoning that makes each row of it stick.

Names and free text: text is the default

Start with the simplest column there is: a name, a note, a description, any human-readable string. The rule is simple, and it surprises almost everyone coming from another database.

Strings are text, with no length number.

If you’ve written SQL before, your hand is already reaching for VARCHAR(255). That reflex is muscle memory from MySQL and SQL Server, where an unbounded string carries a real cost. In Postgres it doesn’t. A text column and a varchar(n) column have identical performance and identical storage, because Postgres stores them the exact same way under the hood. The length cap buys you nothing at the database level. That is counterintuitive enough to be worth pausing on.

The reflex isn’t just neutral, it’s actively harmful. Here are the two side by side.

varchar(n) — the reflex

name: varchar({ length: 255 }),

Feels safe, buys nothing. It looks like type safety, as if it guaranteed names can’t exceed 255 characters, but that 255 is arbitrary. It makes nothing faster or smaller, and the day the business says a name can be 300 characters, you ship a database migration to lift a limit that never earned its keep.

text — the default

name: text(),

Variable length, same performance. No cap to maintain, no migration when the limit changes. If there genuinely is a maximum length, it’s a validation rule rather than a storage rule, and it lives somewhere else entirely.

That last point is the real lesson, so it’s worth stating precisely. A length limit is a user-experience concern, not a storage concern. “Names must be 100 characters or fewer” exists to give the user a friendly error in a form, not to make Postgres truncate their data mid-word. So the cap belongs at the edge of your system, in the validation layer, where a too-long name produces a clear message before it ever reaches the database. You’ll build that layer with Zod in a later chapter. There is one source of truth for “max 100 chars,” and it’s the validator, not the column.

You’ll see this division of labor again and again: the database type says what can be stored, and the validator says what’s allowed in. They’re partners, not substitutes. The column stays text, and the rule lives in Zod.

That’s why varchar(n) is never the right answer in this course, and char(n), its fixed-width cousin, even less so. char(n) blank-pads every value out to n characters, which is almost never what anyone wants. They’re worth naming here so you recognize them as the types to walk past.

Money and numbers: the four-way decision

This section carries the highest stakes in the lesson. Get a text column wrong and a name is slightly too long. Get a money column wrong and your numbers are quietly, permanently incorrect, with nothing crashing to tell you.

There are four number types in play, and the whole section is one decision among them: integer and bigint for whole numbers of different sizes, numeric for money and anything where exactness is the point, and the floating-point pair (real and double precision), which you’ll learn to keep away from money. We’ll start with the costliest mistake and work outward.

Money is where the real damage lives, so start there. Suppose you store a price as a floating-point number, the same kind of number JavaScript uses for 0.1 + 0.2. Run that in any console and you get 0.30000000000000004, because floats can’t represent most decimal fractions exactly; they store the closest binary approximation. One price with one tiny rounding error doesn’t matter. But sum thousands of invoice lines and the errors accumulate, the cents drift, and the total a customer is charged stops matching the sum of the rows. The books don’t balance, and the code never threw an error: it just computed the wrong answer. Nothing surfaces the mistake until an accountant finds it.

The fix is a type built for exactly this. Here is the money decision in full.

Float — the silent corruption

amountDue: doublePrecision(),

Looks fine, runs fine, wrong. doublePrecision (and its smaller sibling real) store binary floating point. The math executes without error, the totals are subtly wrong, and the bug ships precisely because nothing complains. Never use it for money.

numeric — exact

amountDue: numeric({ precision: 12, scale: 2 }).notNull(),

Exact decimal, the course default for prices. numeric stores the literal digits, so there’s no binary rounding to corrupt the answer. precision: 12, scale: 2 means up to twelve significant digits total, two of them after the decimal point, so ten digits before the decimal and two after. That comfortably covers any SaaS invoice amount.

That amountDue upgrade is deliberate. Last lesson you stubbed amountDue as an integer placeholder, and from here on money is numeric. The integer was a hold-the-spot value while you learned the table shape. Now you know the right type, and numeric({ precision: 12, scale: 2 }) is the standing default for any price column in the course.

Two new words just showed up. Precision is the total count of significant digits the column stores. Scale is how many of those sit after the decimal point. So precision: 12, scale: 2 means twelve digits total, two of them cents.

The surprise: numeric comes back as a string

Here’s a detail that trips people up the first time, and it’s the same shape of surprise you met last lesson. When you read a numeric column, Drizzle hands it to your TypeScript as a string, not a number.

That feels wrong until you see why it’s right. A JavaScript number is a 64-bit float, the very thing numeric exists to avoid. If Drizzle handed you a number, it would push your exact decimal back through floating-point math and reintroduce the rounding bug you chose numeric to escape. So instead it gives you the digits faithfully, as a string, and you do money arithmetic with a decimal library that never touches raw +. The string isn’t a defect; it’s the precision guarantee reaching all the way up into your TypeScript. So never parseFloat a money value to add it.

Recall the “two worlds” idea from last lesson, where the TypeScript name amountDue and the SQL name amount_due differ across the boundary. This is the same boundary showing up in a second way: not the name this time, but the runtime type. numeric is the one type where TypeScript and SQL disagree about what the value is, and now you know it’s on purpose. (You’ll see exactly how Drizzle decides this when we reach inferred types at the end of the chapter. For now, just expect the string.)

integer versus bigint

Not every number is money. For plain counts, like a quantity, a number of seats, or a retry counter, reach for integer. It’s a 32-bit signed integer, so it tops out around ±2.1 billion, which is plenty for almost anything you’d count in an app.

When a value can outgrow two billion, such as a high-volume event counter or a Unix timestamp stored as milliseconds, step up to bigint, the 64-bit integer. One wrinkle to know about: a true 64-bit integer is larger than JavaScript’s number can safely hold, so Drizzle’s bigint builder takes a mode. bigint({ mode: 'number' }) gives you an ordinary number and is safe up to about 2⁵³; bigint({ mode: 'bigint' }) gives you a JavaScript BigInt for the full 64-bit range. Reach for mode: 'number' by default and only switch when you genuinely need the top end. We won’t dwell on bigint here; it comes back as a primary-key strategy in a couple of lessons, where the choice has real weight.

Booleans: two flags or one enum

Use boolean for a true-or-false value. That part is straightforward:

isArchived: boolean().notNull().default(false),

The real content here isn’t the type; it’s a modeling decision that comes up the moment you have more than one true/false fact about a row. Do you reach for several booleans, or one of something else?

The test is whether the facts are independent. Take an invoice. isPaid and isSent are orthogonal: an invoice can be sent but unpaid, paid but never sent, both, or neither. Every combination is a real state, so two booleans is exactly right, because they vary freely.

Now take a different fact about that invoice: its status, which is exactly one of draft, sent, paid, or void at any moment. These are mutually exclusive, since it can’t be both draft and paid. Model that with booleans (isDraft, isPaid, isVoid, and so on) and you’ve invited nonsense: nothing stops two of them being true at once, an impossible state your database now permits. A mutually-exclusive set of states wants a single column that holds exactly one value, and that’s an enum, which is two sections away. So use booleans when the facts are independent, and one enum when exactly one can be true at a time.

Time: timestamptz is the only timestamp

Every timestamp you write in this course follows one hard rule:

Every timestamp is timestamp({ withTimezone: true }).

That maps to the Postgres type timestamptz , which stores an absolute instant in UTC and converts on the way in and out. There’s a plain timestamp without the time-zone option, and it is never the right answer here. Choosing it is the single most common timezone bug in Drizzle codebases, so it’s worth seeing the failure concretely before the rule.

Picture a createdAt stored as a plain timestamp. On your laptop it works flawlessly: you write “2pm,” you read “2pm.” Then you deploy to a server in another region, or a user in a different timezone loads the row, and “created at 2pm” silently means 2pm in some other zone, off by hours. The data looks fine. It type-checks. It passes every test you ran on one machine. The bug stays hidden until the moment a region boundary gets crossed, which is usually production.

Plain timestamp — the hidden bug

createdAt: timestamp(),

A wall-clock with no zone. It stores “2pm” with no record of 2pm where. It’s correct on exactly one machine, and wrong the day you deploy across regions or a user in another timezone reads it. It stays invisible until that day.

timestamptz — always

createdAt: timestamp({ withTimezone: true }).defaultNow().notNull(),

Stores an absolute instant in UTC. Converts on read, so it means the same moment in every timezone on earth. This is the only timestamp shape the course writes. (.defaultNow() fills in the current time on insert and .notNull() forbids an empty value. Both are named here at face value; the next lesson covers them. This is just the canonical createdAt line, so it’s familiar when you see it.)

The rule has a clear reason behind it. An event happened at one real instant, and timestamptz is the only type that records which instant. There is exactly one honest exception, worth knowing so you recognize it when it genuinely applies: a recurring local time of day that must stay local no matter the zone. “The store opens at 9am local time” is not a single instant; it’s 9am in Tokyo and 9am in Berlin, different moments. You model that as a time column plus a separate timezone text column, never a timestamptz. It’s a rare carve-out, not a tool you reach for often.

Watch video

CockroachDB walks through the timestamp vs timestamptz choice in 3 min, showing the same row render differently per session zone and why timestamptz is the one to reach for.

Calendar dates and time-only

Not everything about time is an instant. A few more time-shaped types round this out. They’re still “time,” so they live here rather than in their own section.

A date is a calendar day with no time and no zone: a due date, a billing date, a birthday. It’s the right type whenever the answer is “a day,” and it comes with a trap worth naming. Don’t model a calendar day as a timestamptz at midnight. Midnight in which zone? The instant “2026-03-14 00:00” lands on different calendar days depending on the reader’s zone, so a birthday stored that way can shift by a day. A date has no zone and means the same day everywhere, which is exactly why it exists.

Two more you only need to recognize: time is a time of day with no date attached, and interval is a span of time (three days, two hours). It’s enough to know they exist.

One pattern is worth flagging now and deferring: every SaaS table tends to carry both a createdAt and an updatedAt. Typing those two timestamp columns onto every single table by hand gets repetitive fast. There’s a tidy way to factor them out into a reusable set of columns, and that’s the next lesson’s job. Know it’s coming, but don’t build it yet.

IDs: uuid for surrogate keys

Every row needs an identifier. The type for it is uuid, which holds a 128-bit UUID and, like the other types that surprised you, maps to a TypeScript string.

The point worth recognizing here is that a uuid column can generate its own default value, and there are two generators you’ll see in the wild. Either of these goes on the id column.

id: uuid().defaultRandom(),
id: uuid().default(sql`uuidv7()`),

The first, .defaultRandom(), calls Postgres’s built-in gen_random_uuid() and produces a fully-random UUIDv4 . The second produces a UUIDv7 , whose leading bits encode a timestamp so the IDs sort by creation time, which is gentler on the database’s indexes. Postgres 18, which this course pins to, ships uuidv7() natively, with no extension to install.

Which one you should choose is a real decision with a real trade-off, and it gets its own treatment a couple of lessons from now, alongside the question of whether to use uuid at all or a bigint identity instead. The reasoning can wait. For now the goal is recognition: uuid is the type, it surfaces as a string, and it has these two default generators.

A fixed set of choices: pgEnum

Recall the invoice status from the booleans section, exactly one of draft, sent, paid, or void. That’s the textbook case for an enum, and Drizzle spells it with pgEnum. It’s shaped differently from every builder you’ve seen so far, so it’s worth reading closely.

export const invoiceStatus = pgEnum('invoice_status', [
  'draft',
  'sent',
  'paid',
  'void',
]);

export const invoices = pgTable('invoices', {
  // ...
  status: invoiceStatus().notNull().default('draft'),
});

It’s a two-step shape. The call pgEnum('invoice_status', [...]) does two things at once: it declares a real, named Postgres enum type called invoice_status, and it returns a column builder. You export that builder at the top of the file, where it becomes part of your schema’s surface, just like a table, and then you call invoiceStatus() down in the column map, exactly the way you’d call text() or uuid(). You declare it once and then use it as a column.

The payoff arrives later, when you read rows back: the allowed values come through to TypeScript as a union, 'draft' | 'sent' | 'paid' | 'void'. The compiler then knows a status can only ever be one of those four, so a typo like 'snet' won’t compile. That’s making impossible states unrepresentable , a principle you’ve met before, enforced right at the database boundary.

So when do you reach for pgEnum, and when does it stop being the answer? Reach for it when the set of values is small, stable, and mutually exclusive, which an invoice status fits perfectly. The alternative is a lookup table: a separate table with one row per allowed value. You promote to a lookup table the moment the values need their own data hanging off them, like a display label, a color, or a sort order, or the moment non-engineers need to add and remove values at runtime, or the set just keeps growing. Metadata, runtime editing, or open-endedness is the signal to graduate from an enum to a table.

One watch-out decides most borderline cases. Enum values are easy to add and painful to remove. Adding archived to the list later is a small change, but dropping a value is a harder migration: you first have to prove that no row anywhere still uses it. So enums are for genuinely stable sets. If you can already picture yourself deleting one of these values someday, that’s a hint it wanted to be a lookup table. (The mechanics of changing enum values live in a later migrations chapter, named but not taught here.)

A flexible blob: jsonb with $type

Sometimes a column’s shape isn’t yours to define: an incoming webhook body from Stripe, the details bag on an audit-log entry, a grab-bag of per-tenant settings, data that’s genuinely shapeless or dictated by a third party. For that, Postgres has jsonb, which stores structured JSON in a binary, indexable, queryable form.

The course rule is short: jsonb, never json. Postgres has both. Plain json stores the raw text of the document; jsonb parses it into a binary representation that the database can actually index and query into. There’s no upside to plain json for what you’ll build, so it’s worth knowing only as the foil; always reach for jsonb.

Drizzle adds a useful move on top. By default, a JSON column comes back to TypeScript as unknown, because the database has no idea what’s inside, so neither does the compiler, and every place you read it has to cast first. You can do better: .$type<WebhookEvent>() annotates the column with the TypeScript shape you expect, so reads come back typed. Three separate things happen in that one line, so it’s worth stepping through.

import { jsonb, pgTable } from 'drizzle-orm/pg-core';
import type { WebhookEvent } from '@/lib/webhooks';

export const webhookDeliveries = pgTable('webhook_deliveries', {
  // ...
  payload: jsonb().$type<WebhookEvent>().notNull(),
});

The builder. jsonb stores binary, indexable JSON, and the course never uses plain json. On its own, a read of this column would land in your code as unknown.

import { jsonb, pgTable } from 'drizzle-orm/pg-core';
import type { WebhookEvent } from '@/lib/webhooks';

export const webhookDeliveries = pgTable('webhook_deliveries', {
  // ...
  payload: jsonb().$type<WebhookEvent>().notNull(),
});

The annotation. It tells Drizzle the TypeScript shape of what’s inside, so reads come back as WebhookEvent instead of unknown and you skip a cast at every read site. This is the Drizzle-specific move worth knowing.

import { jsonb, pgTable } from 'drizzle-orm/pg-core';
import type { WebhookEvent } from '@/lib/webhooks';

export const webhookDeliveries = pgTable('webhook_deliveries', {
  // ...
  payload: jsonb().$type<WebhookEvent>().notNull(),
});

The catch. $type is a compile-time promise, not a runtime check. Postgres stores whatever bytes you write, so if the real payload doesn’t match WebhookEvent, the compiler won’t catch it. Validating the actual shape on the way in is Zod’s job at the boundary, in a later chapter. $type says what you expect; Zod is what enforces it.

← Previous 1 / 1 Next →

That third step is the one most often missed, so it’s worth dwelling on: a $type annotation is a claim TypeScript trusts, not a guarantee Postgres verifies. The database is happy to store a payload that doesn’t match, and your reads will confidently hand you the wrong type. The runtime gate is always Zod.

One rule keeps a jsonb column from slowly degrading your schema. Reach for it for data that is genuinely shapeless or third-party-defined: webhook bodies, audit-log details, flexible metadata. Skip it for anything you’ll filter, sort, or join on. The heuristic is simple: the moment you find yourself reaching into the JSON inside a WHERE clause more than occasionally, that field wanted to be a real column. A jsonb value you keep querying into is normalization debt, and promoting it to a column lets the database index it properly. (Querying into jsonb with its special operators is a skill for a later chapter. Here, just know that needing to is the signal you reached for jsonb when a column was the right call.)

A small ordered list: text().array()

Postgres has native array columns, and Drizzle exposes them by chaining .array() onto a builder: text().array() is an ordered list of strings in a single column, integer().array() a list of integers, and so on. The classic example is a tags column:

tags: text().array().notNull().default([]),

This is the lightweight option, ideal when you need a small, ordered list of plain scalars and a whole separate table would be overkill. Its limitation is exactly what tells you when to stop using it: array elements aren’t real database rows. They can’t be foreign keys, they can’t be JOINed, and they get no referential integrity. Postgres sees a bag of strings, not connections to anything.

So the array has outgrown itself the moment those tags need to become entities, with their own id, color, and owner that you rename in one place and see reflected everywhere, or the moment you need to efficiently answer “every invoice tagged urgent” at scale. At that point it wants a junction table , and building that is a whole lesson later in this chapter. The trigger to make the jump is a single question: would I ever want to JOIN on these, or give them attributes of their own? If yes, it was always a junction table, and the array only postponed the work. Arrays are tempting because they’re so easy to reach for, so a text[] of tags that the product later wants to rename, recolor, or filter the whole list by is really a postponed junction table.

Network addresses: inet

One small choice quietly marks an experienced hand. When you store an IP address, like an audit log’s actor IP or a request’s origin, most people default to text. Postgres has a real type for it:

actorIp: inet().notNull(),

inet stores IPv4 and IPv6 addresses as genuine network values, which means the database can sort them correctly and run subnet and range queries (such as “is this IP inside that block?”) that a plain string simply can’t. It’s a small upgrade with no downside: stored as inet rather than text, an IP is a real address the database understands instead of an opaque string. This pays off when audit-log tables arrive later in the course.

Named and deferred: types this course handles elsewhere

A few Postgres types are real and useful but belong to later chapters or to a different layer of the stack entirely. They’re listed here for one reason: so that when you don’t find them in the defaults table below, you know they were left out on purpose, not forgotten.

Geographic data

point, polygon, and geography are the PostGIS family for maps and spatial queries. Out of scope for this course.

Full-text search

tsvector, built as a generated column, powers search across text. A later chapter on querying covers it.

Binary blobs

bytea stores raw bytes. This course deliberately doesn’t put files in Postgres; they go to object storage (R2) and the table keeps a text URL or key. It’s an architectural choice, named here so it isn’t a surprise later.

The per-column defaults table

Here’s the artifact the whole lesson was building toward: the lookup you’ll actually reach for on every future column. The kind of column is on the left, and the Drizzle builder you default to is on the right. This is the one to bookmark.

Kind of column

Reach for

Name / free text

text()

Money

numeric({ precision: 12, scale: 2 })

Count / small number

integer()

Big number / Unix-ms

bigint({ mode: 'number' })

True / false

boolean()

Timestamp

timestamp({ withTimezone: true }).defaultNow()

Calendar day

date()

ID (surrogate key)

uuid()

Fixed set of states

pgEnum(...)

Flexible payload

jsonb().$type<T>()

Small scalar list

text().array()

IP address

inet()

Pick the type from the left column. The modifiers (.notNull(), defaults, keys) come in the next two lessons; this table is about choosing the type.

Two notes on reading it. The modifiers you see hanging off a few rows, .defaultNow() especially, are about to get their own lesson, so ignore them for now and read each row as “this kind of column maps to this type.” And the uuid() row hides the v4-versus-v7 default generator decision you saw earlier, which is also coming up shortly. The table is about picking the type; the rest is the next two lessons.

Practice: type each column

Reading the table is one thing; reaching for the right builder under your own fingers is the skill. Below, the organizations table is done for you. Your job is to complete an invoices table whose columns force every major decision from this lesson. The grader checks the emitted SQL type, so an integer where money belongs, or a timestamp without a time zone, fails here exactly as it would fail your future self.

Complete the invoices table — aim for an all-green checklist. Pick the correct Postgres type for each column: the grader reads the emitted SQL type, so an integer where money belongs, or a timestamp without a time zone, will fail. Write the keys in camelCase. The invoiceStatus enum is already declared for you; use it for the status column.

Check schema Feedback Reset

Quick check: sort each column to its type

That drill made you recall the builders. This one is faster and catches the gap in between: recognizing a type when you see it but not yet being able to summon it from a plain description. Drag each real-world column to the Postgres type a 2026 SaaS would reach for.

Sort each column into the Postgres type a 2026 SaaS would reach for. Drag each item into the bucket it belongs to, then press Check.

numeric exact decimal — money

timestamptz timestamp with time zone

text variable-length string

jsonb flexible JSON payload

pgEnum fixed set of states

date calendar day, no time

An invoice’s total amount

A product’s unit price

When a row was created

When an email was sent

A customer’s name

A free-form note on an order

A third-party webhook’s raw body

A per-tenant settings blob

Invoice status: draft / sent / paid / void

An invoice’s due date

A user’s birthday

Check Reset

One last question covers the sharpest trap, the one that silently corrupts data if you get it wrong.

An invoice amount should be numeric, never double precision. What goes wrong if you store it as double precision?

The math runs without complaint, but each value is stored as the nearest binary approximation of the decimal, so summed amounts drift by fractions of a cent and totals stop matching the rows.

double precision can’t hold a number large enough for a realistic invoice total, so big amounts overflow.

Postgres rejects the insert outright, because double precision columns refuse any value with digits after the decimal point.

double precision truncates every value to a whole number on write, silently dropping the cents.

Closing

Here’s the whole lesson in short: a 2026 SaaS reaches for about eight Postgres types, and each is shadowed by one tempting but wrong alternative you now know to walk past. Use text, not varchar. Money is numeric, never a float, and a timestamp is timestamptz, never a plain timestamp. Reach for jsonb typed with $type for genuinely shapeless blobs, but a real column for anything you’ll filter on. A small, stable, mutually-exclusive set of states is a pgEnum, and a light scalar list is text().array() until the day it grows attributes and graduates to a junction table. IP addresses are inet. Keep the defaults table within reach; it’s the practical residue of everything here.

Every column in your schema now has a type. What it doesn’t yet have, by design, is its three per-column modifiers: can it be null, does it carry a default, is it generated? Those are the calls you’ve been writing on faith all lesson, like .notNull() and .defaultNow(), and the next lesson finally explains them, starting with the default that quietly shapes every well-built table: not-null until proven otherwise.

External resources

Drizzle ORM — PostgreSQL column types

orm.drizzle.team

The canonical builder reference for every type in this lesson.

PostgreSQL 18 — data types

postgresql.org

The official type reference, including the native uuidv7() generator new in Postgres 18.

Why floats can't represent money

0.30000000000000004.com

A short, language-by-language demonstration of the floating-point rounding that makes numeric mandatory for currency.

PostgreSQL wiki — Don't Do This

wiki.postgresql.org

The community's anti-pattern list, independently reaching the same verdicts: text over varchar(n), timestamptz over timestamp, never floats for money.