Catalog My learning Blog
PostgreSQL Course: Beginner to Production in 30 Days · Phase 1 · Lesson 4 · 6 min read

PostgreSQL Data Types Explained

On this page
  1. Choosing PostgreSQL data types
  2. integer, bigint and numeric for money
  3. text vs varchar vs char
  4. timestamp vs timestamptz
  5. UUIDs, arrays and enums
  6. JSON: default to jsonb
  7. Check yourself

Choosing PostgreSQL data types

Picking a type takes seconds. Changing it later can mean rewriting a table with a billion rows while the application waits. A good type also catches bad data for free: a date column rejects "next Tuesday", and a text column accepts anything.

The rule that works: pick the type that matches what the value means, not the smallest one that fits today. Here are the decisions you'll make most often.

integer, bigint and numeric for money

TypeSizeUse it for
integer4 bytes, up to about 2.1 billionMost counts and IDs. The default.
bigint8 bytes, up to about 9.2 quintillionIDs on tables that could pass 2 billion rows, event counters
smallint2 bytes, up to 32,767Rarely worth it. The saving is tiny.
numeric(p, s)VariesMoney and anything that must add up exactly
real, double precision4 and 8 bytesMeasurements and science, where tiny rounding is fine

The money row is the one that matters. Floating-point types store binary fractions, and most decimal fractions, like 0.1, can't be stored exactly. Try it:

Code
SELECT 0.1::double precision + 0.2::double precision;   -- 0.30000000000000004
SELECT 0.1::numeric + 0.2::numeric;                     -- 0.3

Those tiny errors add up across thousands of invoice lines. Use numeric for money, always.

For auto-numbered primary keys you'll see SERIAL in older tutorials. It isn't a real type. It's shorthand for an integer column plus a sequence. New code should use identity columns, which are part of the SQL standard:

SQL
CREATE TABLE products (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku         text NOT NULL,
  price       numeric(10,2) NOT NULL,
  weight_kg   real,
  view_count  bigint NOT NULL DEFAULT 0
);

GENERATED ALWAYS also stops code from inserting its own id by accident. Day 20 compares the two in detail.

text vs varchar vs char

PostgreSQL has char(n), varchar(n) and text. Unlike some databases, they're all stored the same way and perform the same. So the choice is only about rules:

  • text has no length limit. Use it by default.
  • varchar(n) rejects anything longer than n characters. Use it when a limit is a real business rule.
  • char(n) pads short values with spaces. That surprises people when they compare or concatenate values, so avoid it.

If you need a limit you might change later, text plus a CHECK constraint is easier to adjust. Day 5 shows how.

timestamp vs timestamptz

There are five time types: date, time, timestamp, timestamptz and interval. The one decision that causes real bugs is timestamp versus timestamptz.

The names mislead. timestamptz doesn't store a time zone. It stores one exact moment, kept in UTC, and shows it in whatever zone your session uses. Plain timestamp stores the wall-clock digits you gave it and has no idea which zone they belong to.

The same INSERT stored two ways. timestamptz converts on the way in and out; timestamp keeps the raw digits.
The same INSERT stored two ways. timestamptz converts on the way in and out; timestamp keeps the raw digits.

See it for yourself:

SQL
CREATE TABLE events (
  id          int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  is_public   boolean NOT NULL DEFAULT true,
  starts_on   date NOT NULL,
  duration    interval NOT NULL DEFAULT '1 hour',
  created_at  timestamptz NOT NULL DEFAULT now()
);
INSERT INTO events (starts_on) VALUES ('2026-11-02');

SET TIME ZONE 'America/New_York';
SELECT created_at FROM events;
SET TIME ZONE 'Asia/Tokyo';
SELECT created_at FROM events;   -- same moment, different clock

So:

  • Use timestamptz for anything that happened at a real moment: orders placed, logins, messages sent.
  • Use timestamp only for times with no zone, like "the shop opens at 09:00 local time".
  • Use date when there's no time of day, such as a birthday.

UUIDs, arrays and enums

These three solve specific problems. Each has a catch.

UUID is a 128-bit identifier that any system can generate without asking the database for the next number. That's useful when mobile apps or several services create records offline. gen_random_uuid() has been built in since PostgreSQL 13. PostgreSQL 18 adds uuidv7(), which starts with a timestamp, so new values arrive roughly in order. That keeps indexes compact.

SQL
CREATE TABLE sessions (
  id          uuid PRIMARY KEY DEFAULT uuidv7(),
  user_agent  text
);

The catch: a UUID is 16 bytes against 8 for a bigint, and random v4 UUIDs scatter inserts across the whole index.

Arrays hold a list in one column. Any type can be an array:

SQL
CREATE TABLE articles (
  id     int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title  text NOT NULL,
  tags   text[] NOT NULL DEFAULT '{}'
);
INSERT INTO articles (title, tags) VALUES ('Intro to SQL', ARRAY['sql', 'beginner']);
SELECT title FROM articles WHERE tags @> ARRAY['beginner'];   -- @> means "contains"

The catch: once you need to join on the items, count them across rows or attach details to each one, a separate table works better.

ENUM limits a column to a fixed list of labels, and the database enforces it:

SQL
CREATE TYPE order_status AS ENUM ('pending', 'paid', 'shipped', 'cancelled');
CREATE TABLE orders (
  id      int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  status  order_status NOT NULL DEFAULT 'pending'
);
ALTER TYPE order_status ADD VALUE 'refunded';

The catch: you can add a label and rename one, but you can't remove one. If the list changes often or each value needs extra details, use a lookup table instead.

JSON: default to jsonb

There are two JSON types. json stores the text exactly as you sent it. jsonb parses it into a binary format, which is a little slower to write and much faster to query, and it can be indexed. Use jsonb unless you must keep the original formatting or key order.

Code
SELECT '{"theme": "dark", "beta": true}'::jsonb ->> 'theme';   -- dark

-> returns a JSON value and ->> returns text. Day 18 goes much deeper.

Check yourself

Floating point can't store most decimal fractions exactly, so small rounding errors build up as lines are added. Store money as numeric with a fixed scale, such as numeric(12,2).

Each server wrote its own local wall-clock time with no zone attached, so the same digits mean different moments. Use timestamptz, which stores one exact moment and converts it for each session.

PostgreSQL can add or rename ENUM labels but can't remove one. You'd need a new type and a migration of every column using it, which is why changing lists belong in a lookup table.

Usually not in PostgreSQL. text and varchar are stored the same way and perform the same, and the 255 limit is a habit from other databases with no real meaning here. Use text, and add a CHECK (length(name) <= 200) only if the business has an actual limit.

For data whose shape varies or that you mostly store and return as a whole, such as third-party webhook payloads or optional per-product attributes. Use normal columns for fields you filter, join or constrain often, because columns get type checks, constraints and simpler indexes. A mix is common.