Data Types in Depth
PostgreSQL's type system goes far beyond the SQL standard basics, offering native support for arrays, JSON/JSONB, ranges, network addresses, geometric types, and user-defined composite and enum types, all of which are indexable and queryable with the same operator and index infrastructure as built-in types. Choosing the right type isn't just about correctness -- it directly affects storage size, index efficiency, and which operators and functions are available, so understanding the trade-offs between similar-looking types like numeric vs. double precision or text vs. varchar(n) matters for both performance and data integrity.
Cricket analogy: Choosing a PostgreSQL type is like choosing a bat for a given pitch -- a heavy bat (numeric) gives you precise power for slow turning pitches while a lighter bat (real) is faster but less exact, and picking wrong costs you either performance or accuracy.
Numeric Types and Precision
The numeric type stores arbitrary-precision decimal values exactly, making it the correct choice for money and any value where rounding errors are unacceptable, at the cost of slower arithmetic compared to native machine types. In contrast, real and double precision are IEEE 754 floating-point types that are fast but can only approximate most decimal fractions, so 0.1 + 0.2 may not equal exactly 0.3 -- fine for scientific measurements, dangerous for financial totals. Integer types (smallint, integer, bigint) and the serial/identity variants round out the numeric family for counters and primary keys.
Cricket analogy: It's like the difference between the third umpire's exact ball-tracking data (numeric) used for a DRS review versus a commentator's rough visual estimate (float) of where the ball pitched -- only one is trusted for the final decision.
Text, JSONB, and Arrays
In PostgreSQL there is effectively no performance difference between text, varchar, and varchar(n) -- all are stored the same way, so varchar(n) is really just a length constraint, not a distinct storage optimization the way it is in some other databases. JSONB stores JSON in a decomposed binary format that supports indexing (via GIN) and efficient containment queries (@>, ?, #>), making it a solid choice for semi-structured data that still needs to be queried relationally, while native array types (integer[], text[]) let a column hold an ordered list of values without a separate join table for simple cases.
Cricket analogy: JSONB is like a flexible scouting report that can hold varying fields per player (some have bowling stats, some don't) while still letting the analyst quickly filter for 'all players who bowl left-arm spin', unlike a rigid fixed-column spreadsheet.
-- JSONB with a GIN index for fast containment queries
CREATE TABLE products (
id serial PRIMARY KEY,
name text NOT NULL,
price numeric(10,2) NOT NULL,
tags text[] DEFAULT '{}',
attributes jsonb DEFAULT '{}'
);
CREATE INDEX idx_products_attributes ON products USING GIN (attributes);
CREATE INDEX idx_products_tags ON products USING GIN (tags);
-- Query rows where attributes contains a given key/value
SELECT name FROM products WHERE attributes @> '{"color": "red"}';
-- Query rows where tags array contains a value
SELECT name FROM products WHERE tags @> ARRAY['sale'];Prefer JSONB over JSON in almost all cases: JSONB stores a parsed binary representation that supports indexing and is faster to query, while plain JSON only preserves exact text formatting (whitespace, key order) and re-parses on every access.
Enums and Domains for Data Integrity
A CREATE TYPE ... AS ENUM defines a fixed, ordered set of allowed text labels stored internally as a compact 4-byte value, giving you both the readability of text and the storage efficiency and validation of a constrained set -- useful for things like order_status or subscription_tier where the valid values rarely change. A CREATE DOMAIN wraps an existing base type with a reusable constraint (e.g. a domain email_address as text with a CHECK that it matches an email pattern), letting you define a validation rule once and reuse it across many table columns instead of repeating the same CHECK constraint everywhere.
Cricket analogy: An enum is like a fixed list of valid dismissal types (bowled, caught, lbw, run out) that a scorer must pick from -- you can't accidentally log 'dismissed by aliens', unlike a free-text field.
- text, varchar, and varchar(n) share identical storage in PostgreSQL; varchar(n) only adds a length check.
- Use numeric for money and any value that must avoid floating-point rounding error.
- real and double precision are fast IEEE 754 approximations, unsuitable for exact financial totals.
- Prefer JSONB over JSON for anything you intend to query or index, since it supports GIN indexes.
- Native array columns can replace a join table for small, simple, ordered lists.
- Enum types validate a fixed set of labels while storing compactly as a 4-byte value.
- Domains let you define a reusable constrained type once and apply it across many columns.
Practice what you learned
1. What is the practical storage difference between text and varchar(n) in PostgreSQL?
2. Why should numeric be preferred over double precision for currency values?
3. What advantage does JSONB have over plain JSON?
4. What does a CREATE DOMAIN statement primarily let you do?
5. How are enum values stored internally in PostgreSQL?
Was this page helpful?
You May Also Like
PostgreSQL Architecture Overview
A tour of how PostgreSQL is built internally: the process model, shared memory, the write-ahead log, and the MVCC storage engine that together deliver durability and concurrency.
Constraints in Depth
A thorough look at PostgreSQL's constraint types -- PRIMARY KEY, FOREIGN KEY, CHECK, UNIQUE, NOT NULL, and EXCLUDE -- and how they enforce data integrity at the database layer.
Schemas and Namespaces
How PostgreSQL uses schemas as namespaces within a database to organize objects, control search_path resolution, and isolate multi-tenant or multi-team data.