Lesson 05 beginner

PostgreSQL Data Types

Choose the right type for every column: integers and identity keys, numeric versus floating point for money, text and its length limits, booleans, dates, timestamps with and without time zone, intervals, UUIDs including the new uuidv7, enums and domains, arrays and jsonb, and how casting works.

Every column has a type, and the type decides what values are allowed, how much space they take, which operators and indexes work on them and how they sort. Picking good types up front avoids most data-quality bugs. This lesson tours the types you will use daily and adds a subscription plan to Orbit’s users.

The short list

KindUseAvoid
Whole numbersbigint for ids and counters, integer for small countssmallint unless space really matters
Exact decimalsnumeric(12,2) for money and quantitiesreal / double precision for money
Approximatedouble precision for measurements and science
Texttext, with a CHECK if you need a limitchar(n)
Yes / nobooleanint flags, 'Y'/'N' strings
Points in timetimestamptztimestamp without time zone
Calendar daysdatestoring dates as text
Durationsintervalseconds in an int
Identifiersuuid when ids are created outside the databasetext UUIDs
Flexible datajsonbjson, unless you need the exact original text

Numbers: exact versus approximate

integer (4 bytes) holds about ±2.1 billion and bigint (8 bytes) about ±9.2 quintillion. Postgres refuses to overflow silently:

SELECT 2147483647::integer + 1;
ERROR:  integer out of range

For decimals, the choice matters more than it seems. double precision is binary floating point, fast but approximate. numeric stores decimal digits exactly:

SELECT 0.1::double precision + 0.2::double precision AS float_sum,
       0.1::numeric + 0.2::numeric AS numeric_sum;
      float_sum      | numeric_sum
---------------------+-------------
 0.30000000000000004 |         0.3
(1 row)

That is why money belongs in numeric. Give it a precision and scale, numeric(12,2), and Postgres rounds to two decimals on the way in:

SELECT 19.999::numeric(10,2);
 numeric
---------
   20.00
(1 row)

Dividing two integers gives an integer (7 / 2 is 3). Cast one side to numeric when you want the fraction.

Text

text, varchar and varchar(n) are stored the same way in Postgres, with no performance difference. varchar(n) only adds a length check, and that check has a surprise: an explicit cast truncates instead of failing.

SELECT 'orbital'::varchar(5);
 varchar
---------
 orbit
(1 row)

Inserting 'orbital' into a varchar(5) column, on the other hand, fails with value too long for type character varying(5). Most teams use plain text and add a constraint they can change later without rewriting the table:

ALTER TABLE notes ADD CONSTRAINT notes_title_length CHECK (char_length(title) <= 200);

Avoid char(n): it pads values with spaces, which then disappear or reappear depending on the operation.

Booleans

boolean holds true, false or NULL. Postgres accepts several spellings on input and always prints t or f:

SELECT 'yes'::boolean, 'off'::boolean, 't'::boolean;
 bool | bool | bool
------+------+------
 t    | f    | t
(1 row)

Dates, times and time zones

This is where most bugs hide, so learn the rule once: use timestamptz for anything that happened at a moment in time.

Despite its name, timestamptz (timestamp with time zone) does not store a time zone. It stores an absolute instant, converted to UTC on the way in, and converts it to the session’s timezone on the way out:

SET timezone = 'UTC';
SELECT timestamptz '2026-11-09 12:00:00-03' AS stored_instant;
     stored_instant
------------------------
 2026-11-09 15:00:00+00
(1 row)
SET timezone = 'America/Sao_Paulo';
SELECT timestamptz '2026-11-09 12:00:00-03' AS stored_instant;
RESET timezone;
     stored_instant
------------------------
 2026-11-09 12:00:00-03
(1 row)

Same instant, displayed for two different readers. Plain timestamp (without time zone), by contrast, is just a calendar date and wall-clock time, and silently ignores any offset you give it:

SELECT timestamp '2026-11-09 12:00:00-03' AS wall_clock;
     wall_clock
---------------------
 2026-11-09 12:00:00
(1 row)

date is a calendar day and interval a duration. They combine naturally:

SELECT date '2026-11-09' + 30 AS in_30_days,
       interval '1 day 2 hours' * 3 AS three_times;
 in_30_days |   three_times
------------+-----------------
 2026-12-09 | 3 days 06:00:00
(1 row)

UUIDs

A uuid is a 128-bit identifier that can be generated anywhere, by the database or by clients, without coordination. It is stored in 16 bytes, so always use the uuid type rather than text.

SELECT gen_random_uuid();   -- version 4: completely random
SELECT uuidv7();            -- version 7 (PostgreSQL 18): time-ordered

Random version-4 UUIDs scatter inserts all over a B-tree index, which hurts write performance on big tables. Version-7 UUIDs start with a timestamp, so new values land at the end of the index like an identity column. If you want UUID keys, use DEFAULT uuidv7().

Enums and domains

An enum is a fixed, ordered list of labels. Orbit has two subscription plans:

CREATE TYPE plan AS ENUM ('free', 'pro');
ALTER TABLE users ADD COLUMN plan plan NOT NULL DEFAULT 'free';
CREATE TYPE
ALTER TABLE

You can add labels later with ALTER TYPE plan ADD VALUE 'team';, but not remove them. If the list changes often, a lookup table or a CHECK (plan IN (...)) constraint is easier to evolve.

A domain is a reusable type with built-in rules:

CREATE DOMAIN email_address AS text CHECK (VALUE ~ '^[^@\s]+@[^@\s]+$');

Arrays and JSON

Any type can be an array, and jsonb stores JSON documents in a binary form you can index and query:

SELECT ARRAY['work', 'postgres'] AS tags,
       '{"b": 1, "a": 2, "a": 3}'::jsonb AS doc;
      tags       |       doc
-----------------+------------------
 {work,postgres} | {"a": 3, "b": 1}
(1 row)

jsonb removes duplicate keys (the last one wins), drops insignificant whitespace and stores keys in its own order. Use json only if you must keep the original text exactly. Arrays and JSON are powerful, but they are not a replacement for tables: if you need to join on something, enforce rules on it or count it, it probably wants its own table. Lesson 8 models Orbit’s tags as a table for exactly that reason, and lesson 13 adds a jsonb settings column.

Casting

Convert between types with ::type or the standard CAST(value AS type). pg_typeof tells you what type an expression has:

SELECT pg_typeof(1), pg_typeof(1.5), pg_typeof('x'), pg_typeof(now());
 pg_typeof | pg_typeof | pg_typeof |        pg_typeof
-----------+-----------+-----------+--------------------------
 integer   | numeric   | unknown   | timestamp with time zone
(1 row)

A quoted literal starts out as unknown and takes whatever type the context needs, which is why WHERE created_at > '2026-10-01' just works.

Quick reference

NeedType
Primary keybigint GENERATED ALWAYS AS IDENTITY or uuid DEFAULT uuidv7()
Money, quantitiesnumeric(12,2)
Name, title, bodytext (+ CHECK for limits)
Flagboolean
When it happenedtimestamptz
Birthday, due datedate
Durationinterval
Fixed set of labelsenum or CHECK (... IN (...))
Flexible attributesjsonb
Type of an expressionpg_typeof(expr)

Exercise

An invoice total is stored as double precision and reports show 0.30000000000000004. Which type should the column have?

  1. real
  2. integer
  3. text
  4. numeric(12,2)
Show answer

Answer: D. Floating-point types are binary approximations, so many decimal fractions cannot be stored exactly. numeric keeps exact decimal digits, which is what money needs.

Exercise

A server in São Paulo and one in Lisbon store the same event in a timestamptz column. What is stored on disk?

  1. Each server’s local time and its time zone name
  2. The same absolute instant, normalised to UTC
  3. The time as text, exactly as the client sent it
  4. Only the date part
Show answer

Answer: B. timestamptz converts input to UTC and stores the instant. The session’s timezone setting only affects how it is parsed and displayed.

Exercise

Why are version-7 UUIDs better primary keys than random version-4 UUIDs on large tables?

  1. They start with a timestamp, so new values are appended to the end of the index instead of scattered through it
  2. They are 8 bytes instead of 16
  3. They are guaranteed to be sequential integers
  4. Version-4 UUIDs cannot be indexed
Show answer

Answer: A. Random keys cause writes to touch pages all over a B-tree, which fragments it and evicts cache. Time-ordered keys behave much like an identity column.

Key takeaways

  • Use bigint for ids, numeric for money and double precision only for approximate measurements.
  • text is the default string type; enforce limits with CHECK constraints.
  • timestamptz stores instants; plain timestamp silently ignores time zones.
  • Prefer uuidv7() over random UUIDs for keys on big tables.
  • Enums suit short, stable lists; jsonb suits flexible attributes, not data you join or count.

FAQ

Does choosing varchar(255) over text save space or speed?

No. Postgres stores both the same way and only checks the length for varchar(n). The number 255 is a habit from other databases.

How do I store a time zone with an event, such as a meeting’s local zone?

Store the instant in a timestamptz column and the zone name (for example America/Sao_Paulo) in a separate text column. Convert with AT TIME ZONE when you need the local wall-clock time.

Should I store money as cents in a bigint?

That also works and is common in payment systems, as long as everyone remembers the unit. numeric(12,2) is simpler to read and avoids conversion mistakes.

Is jsonb slower than normal columns?

Reading a whole document is fast, but filtering on keys without an index is slower than on a real column, and the planner has fewer statistics. Put fields you filter, join or sort on in real columns.

What happens to existing rows when I add an enum column with a default?

Every existing row gets the default value, instantly, because a constant default is stored once in the catalog instead of in each row.

↑ ↓ navigate Enter open Esc close