beginnerintermediateadvanced

PostgreSQL · Data Types

PostgreSQL Data Types

Exact vs approximate numerics, text/JSON/array/enum/range extensions, timestamptz vs timestamp, and when a cast happens automatically vs must be written

numeric is exactreal/double precision are approximate — never for money
timestamptz = one instanttimestamp = bare wall-clock numbers, no timezone
JSONB is indexablestored pre-parsed, unlike JSON
text→typed is never implicitalways needs an explicit cast

1.Numeric Types: smallint, integer, bigint, numeric, real, double precision

smallint, integer and bigint are exact whole numbers of increasing range. numeric (aka decimal) is exact with arbitrary precision, ideal for money. real and double precision are approximate floating-point — fast, but not exact, and wrong for money.

Key features

smallint: 2 bytes, ±32,767. integer: 4 bytes, ±2.1 billion. bigint: 8 bytes, ±9.2 quintillion
numeric/decimal stores exact values with user-specified or arbitrary precision — no rounding error
real (4 bytes) and double precision (8 bytes) are approximate IEEE 754 floating-point
0.1 + 0.2 computed in double precision does not equal 0.3 exactly — a floating-point representation artifact, not a PostgreSQL bug
numeric is slower than integer/real for arithmetic, which is the trade-off for exactness

The numeric type family at a glance

The numeric type family at a glance
Type	Storage	Exact?	Typical use
smallint	2 bytes	yes	small counters, enums stored as ints
integer	4 bytes	yes	default choice for whole numbers
bigint	8 bytes	yes	IDs expected to exceed 2 billion rows
numeric(p,s)	variable	yes	money, anything needing exact decimals
real / double precision	4 / 8 bytes	no — approximate	scientific data where tiny error is acceptable

Exact vs approximate — the real dividing line

Exact: integer / bigint / numeric

represents the written value precisely
numeric arithmetic never introduces rounding error
the correct family for money, counts, IDs

Approximate: real / double precision

IEEE 754 floating-point — cannot represent most decimals exactly
0.1 + 0.2 = 0.30000000000000004 in double precision
fine for scientific/measurement data, wrong for money

Command

sql

CREATE TABLE accounts (
    id BIGINT PRIMARY KEY,
    balance NUMERIC(12, 2) NOT NULL,
    measurement DOUBLE PRECISION
);

integer/bigint/numeric are exact; real/double precision are approximate IEEE 754 floating-point that cannot represent most decimals exactly — never use float types for money.

2.Text Types: varchar, char and text

text stores a string of any length with no limit. varchar(n) is the same storage as text but enforces a maximum length of n. char(n) pads every value with trailing spaces to exactly n characters — almost never what modern schemas want.

Key features

text and varchar(n) share the same internal storage in PostgreSQL — no performance difference between them
varchar(n) enforces a maximum length at write time, raising an error if exceeded
char(n) pads every stored value with trailing spaces up to length n, and those spaces are semantically ignored in comparisons
A common default: use text unless there is a real, enforced business reason to cap length, then use varchar(n)
char(n) is rarely appropriate for modern application schemas — it exists mainly for compatibility with older fixed-width conventions

The three text types compared

The three text types compared
Type	Length behavior	Padding
text	unlimited	none
varchar(n)	capped at n, error if exceeded	none
char(n)	fixed at n	pads with trailing spaces

Command

sql

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email TEXT NOT NULL,
    country_code VARCHAR(2)
);

text and varchar(n) share identical storage in PostgreSQL — the only real difference is a length check. char(n) pads with trailing spaces and is rarely the right choice for a modern schema.

3.boolean, bytea, UUID, JSON/JSONB, Arrays, Enums and Range Types

boolean is true/false/NULL. bytea stores raw binary data. UUID stores a 128-bit identifier. JSON/JSONB store semi-structured data (JSONB is binary-parsed and indexable; JSON is text and re-parsed each time). Arrays store a list of one type per column. Enums restrict a column to a fixed set of labels. Range types (like daterange) store a bounded interval as a single value.

Key features

JSONB stores data in a decomposed binary format — supports indexing (GIN) and containment queries; JSON stores exact text and re-parses on every access
bytea stores raw bytes directly in the database — often reconsidered in favor of external object storage for large files
An enum type is fixed at creation; adding a value requires ALTER TYPE, and removing one is not directly supported
A range type like daterange or numrange stores a bounded interval as one value, with built-in overlap operators like &&
An array column stores a list of one element type, but usually cannot enforce per-element foreign key constraints the way a junction table can

When each extension type earns its place over a plain relational column

When each extension type earns its place over a plain relational column
Type	Reaches for it instead of
UUID	a centralized auto-increment sequence, when IDs must be generated client-side or across systems
JSONB	a rigid schema, for genuinely variable or sparse attributes
array	a junction table, for a small list that is never queried by individual element independently
enum	a lookup table, for a small, rarely-changing fixed vocabulary
range (daterange, etc.)	two separate start/end columns plus manual overlap logic

Command

sql

CREATE TYPE mood AS ENUM ('happy', 'sad', 'neutral');

CREATE TABLE events (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tags TEXT[],
    metadata JSONB,
    current_mood mood,
    active_period DATERANGE
);

UUID, JSONB, arrays, enums and range types each replace a specific relational workaround — reach for them when the workaround (a shared sequence, a rigid schema, a junction table, a lookup table, manual interval logic) is genuinely worse than the specialized type, not by default.

4.Date/Time Types: date, time, timestamp, timestamptz, interval

date is a calendar date with no time. time is a time of day with no date. timestamp stores a date and time with no timezone attached. timestamptz stores a date and time normalized to UTC internally, converting to the session's timezone on display. interval represents a duration, like "3 days" or "2 hours."

Key features

timestamptz stores a single, unambiguous instant in time (as UTC internally); timestamp stores wall-clock numbers with no timezone context
timestamptz converts on input to UTC and on output to the session's timezone — the stored value never changes, only its display
timestamp with the same literal in two different sessions with different timezone settings displays identically — it has no idea any timezone applies
interval represents a duration or difference, and can be added to or subtracted from a date/timestamp
The general recommendation is timestamptz for anything representing a real-world event, unless there is a specific reason to store wall-clock-only data

The five date/time types

The five date/time types
Type	Stores	Timezone-aware?
date	a calendar date only	no
time	a time of day only	no (time with time zone exists but is rarely used)
timestamp	date + time, wall-clock	no
timestamptz	date + time, normalized to UTC internally	yes
interval	a duration	not applicable

timestamp vs timestamptz under a changed session timezone

timestamp

stores exactly the wall-clock numbers written
no idea what timezone those numbers meant
displays identically regardless of session timezone

timestamptz

converts input to UTC at write time
stores one unambiguous real-world instant
displays differently per session timezone — same instant, different wall-clock text

Command

sql

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    happened_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    duration INTERVAL
);

timestamptz stores one unambiguous real-world instant (converted to UTC internally); timestamp stores bare wall-clock numbers with no timezone context — default to timestamptz for anything representing a real event.

5.Why numeric Is Usually Preferred Over money

PostgreSQL has a money type — it stores an exact fixed-fraction currency amount and formats it with a currency symbol automatically. In practice, most schemas use numeric(p,s) instead, because money has no configurable currency, rounds silently on assignment, and is locale-dependent in ways that surprise most applications.

Key features

money is exact (like numeric), fast, and formats output with a locale currency symbol automatically
money has no associated currency — a column cannot distinguish USD from EUR without a separate column, and mixing currencies in arithmetic is not prevented
money's output formatting depends on the lc_monetary locale setting, which can differ between environments or change over time
numeric(p,s) with an explicit scale gives predictable, portable precision independent of locale, plus an explicit currency_code column when needed
Casting a value into money silently rounds to the locale's fractional digits, which can surprise a schema expecting arbitrary precision

money vs numeric for currency storage

money vs numeric for currency storage
Property	money	numeric(p,s)
Exact arithmetic	yes	yes
Currency-aware	no — one implicit locale currency	no, but pairs naturally with a currency_code column
Formatting	locale-dependent, automatic	application controls formatting explicitly
Portability across environments	depends on lc_monetary	fully portable — locale-independent

Command

sql

-- typical modern schema: numeric + explicit currency code
CREATE TABLE invoices (
    id SERIAL PRIMARY KEY,
    amount NUMERIC(12, 2) NOT NULL,
    currency_code CHAR(3) NOT NULL DEFAULT 'USD'
);

money's formatting and rounding depend on the server's locale setting, which lives outside the schema — numeric(p,s) with an explicit currency_code column gives portable, schema-defined precision instead.

6.Implicit vs Explicit Casts and Type-Conversion Behavior

An implicit cast happens automatically when PostgreSQL can convert one type to another without ambiguity, like integer to numeric. An explicit cast, written as value::type or CAST(value AS type), is required when the conversion is ambiguous or lossy, like text to integer.

Key features

integer to numeric, numeric to double precision, and most "widening" numeric conversions are implicit
text to any non-text type is never implicit — it always requires an explicit cast, even '123'::integer
value::type is PostgreSQL-specific shorthand; CAST(value AS type) is the portable standard SQL form — both do the same thing
An implicit cast in a WHERE clause comparing mismatched types (e.g. int column vs text literal) can silently prevent index use
Casting a value that cannot be converted raises an error at execution time, not silently producing NULL or a default

Implicit vs explicit — a few common conversions

Implicit vs explicit — a few common conversions
From → To	Implicit or explicit?
integer → numeric	implicit
numeric → double precision	implicit
text → integer	explicit only — '123'::integer
timestamp → date	implicit (truncates the time)
integer → text	explicit only — 123::text

Command

sql

SELECT '42'::integer;
SELECT CAST('42' AS integer);   -- equivalent, standard SQL form

SELECT price::text FROM products;   -- explicit, numeric to text

PostgreSQL only allows an implicit cast when the conversion is safe and unambiguous — text to a typed column is never implicit for an already-typed value, only for an untyped literal being resolved against its comparison target.

7.PostgreSQL-Specific Types vs Portable SQL Types

Portable types (integer, numeric, varchar, timestamp) work the same way, or close to it, across most relational databases. PostgreSQL-specific types (JSONB, arrays, enums, ranges, UUID generation functions) are more expressive and often more efficient in PostgreSQL, but migrating away from PostgreSQL later means rewriting anything built on them.

Key features

JSONB, arrays, range types, and PostgreSQL's specific enum implementation have no direct equivalent in some other relational databases
Standard SQL types (integer, numeric, varchar, date) behave consistently, or close to it, across PostgreSQL, MySQL, SQL Server and others
gen_random_uuid() and other PostgreSQL-specific functions do not exist by that name elsewhere, even though UUID as a concept is portable
Using a PostgreSQL-specific type is a legitimate default when the project has no realistic multi-database requirement
A library or product meant to run on customer-chosen databases usually avoids or abstracts PostgreSQL-specific types deliberately

Portable vs PostgreSQL-specific — a few common choices

Portable vs PostgreSQL-specific — a few common choices
Need	Portable option	PostgreSQL-specific option
Semi-structured data	a TEXT column holding a JSON string	JSONB with indexing/querying
Fixed vocabulary	a lookup table + foreign key	a native ENUM type
A small list	a junction table	an array column
A bounded interval	two separate start/end columns	a range type (daterange, etc.)

Command

sql

-- PostgreSQL-specific, expressive
CREATE TABLE events (id UUID DEFAULT gen_random_uuid(), tags TEXT[], data JSONB);

-- portable equivalent, more verbose
CREATE TABLE events (id VARCHAR(36), data TEXT);
CREATE TABLE event_tags (event_id VARCHAR(36) REFERENCES events(id), tag VARCHAR(50));

PostgreSQL-specific types trade portability for expressiveness and performance — a fine default when there is no realistic multi-database future, and a deliberate risk to weigh explicitly when there is one.

Spotted a mistake on this page? Tell us.

Read Sheet