PostgreSQL Data Types Explained — Which to Use and When

Numeric Types

PostgreSQL ships six standard numeric types, from 2-byte SMALLINT to variable-length arbitrary-precision NUMERIC. The critical split is exact versus approximate storage. The PostgreSQL documentation defines NUMERIC as storing values exactly as entered, with no rounding, up to 131,072 digits before the decimal point. That's what makes it the correct choice for money and any calculation where rounding errors compound.

Key rules:

• NUMERIC and DECIMAL are aliases. They're identical in PostgreSQL. Use NUMERIC(10,2) for monetary columns.
• Never use REAL or DOUBLE PRECISION for money. Both use IEEE 754 binary floating-point, which can't represent most decimal fractions exactly.
• Unlike MySQL, PostgreSQL has no UNSIGNED integers. Use BIGINT if you need a larger positive range than INTEGER provides.
• PostgreSQL doesn't have TINYINT. Use SMALLINT for small integer columns, or BOOLEAN for flags.

Text Types

PostgreSQL's three text types share the same underlying storage engine. That's worth knowing upfront because it changes how you choose between them. The PostgreSQL documentation is explicit: VARCHAR(n), CHAR(n), and TEXT all use identical storage. There's no performance difference between TEXT and VARCHAR in PostgreSQL. You don't need prefix indexes to search a TEXT column, and both can be indexed directly with B-tree or GIN.

Key rules:

• Use TEXT for string columns without a known maximum length. PostgreSQL stores and queries it identically to VARCHAR.
• Use VARCHAR(n) when you want the database to enforce a maximum length at insert time, for example VARCHAR(255) for email addresses.
• Use CHAR(n) only for truly fixed-length values: ISO country codes, currency codes, fixed hash strings.
• Unlike MySQL, TEXT in PostgreSQL doesn't require a prefix length for B-tree indexes and can appear in any WHERE clause without a full-table scan when indexed.

Boolean — Native True/False Storage

PostgreSQL has a native BOOLEAN type that stores true, false, or NULL natively, with no convention required. This is a real difference from MySQL, which has no native boolean type and uses TINYINT(1) as a convention, requiring ORM mapping to handle boolean coercion correctly. The PostgreSQL documentation lists multiple accepted input literals: TRUE/FALSE, 't'/'f', 'yes'/'no', 'on'/'off', and 1/0.

CREATE TABLE user_settings (
    id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id    BIGINT NOT NULL REFERENCES users(id),
    email_opt  BOOLEAN NOT NULL DEFAULT true,
    dark_mode  BOOLEAN NOT NULL DEFAULT false,
    verified   BOOLEAN NOT NULL DEFAULT false
);

In practice, stick to TRUE/FALSE literals in application code. PostgreSQL accepts the abbreviated forms, but explicit literals are clearer in queries and migrations. ORMs like Django, Rails, and Laravel all handle PostgreSQL's BOOLEAN type natively without extra column configuration. Contrast this with MySQL, where ORM-level boolean columns silently map to TINYINT(1) and can store values other than 0 and 1. Building SQL Designer's schema export tool, we ran into exactly this gap when generating MySQL DDL from PostgreSQL boolean columns: the types don't map 1:1, and that difference shows up at runtime.

Date and Time Types

PostgreSQL's datetime type set is more expressive than MySQL's. The most important decision is TIMESTAMP (no timezone) versus TIMESTAMPTZ (with timezone). According to the PostgreSQL documentation, TIMESTAMPTZ stores all values as UTC and converts to the session's TimeZone setting on output. This is behaviorally equivalent to MySQL's TIMESTAMP, but without MySQL's hard ceiling of 2038-01-19.

Key rules:

• Use TIMESTAMPTZ for created_at, updated_at, and any event or log timestamp. It's timezone-safe and has no 2038 problem.
• Use plain TIMESTAMP only when you intentionally want wall-clock time without timezone conversion. That's rare in practice.
• Use INTERVAL for storing durations: subscription lengths, time-since-last-action, recurring event offsets.
• PostgreSQL's DATE range extends to 5874897 AD, and back to 4713 BC. Useful for historical data and long-term scheduling applications.

JSON and JSONB — Binary JSON Storage

PostgreSQL offers two JSON types, and the difference isn't cosmetic. The PostgreSQL documentation explains that JSON stores the input text verbatim, preserving whitespace, key order, and duplicate keys. JSONB, by contrast, parses the document into a binary decomposition on insert. It discards whitespace, deduplicates keys, and rewrites key order. That extra write cost makes subsequent reads and queries significantly faster. JSONB also supports GIN indexes for full-document search and the containment operators (@>, <@) that make querying inside JSON practical at scale.

So when would you pick plain JSON? Almost never. The only reason to reach for it is when preserving exact input representation matters, for example when auditing or replaying an exact API payload. For everything else, JSONB is the right call.

-- Create a table with JSONB for flexible metadata
CREATE TABLE products (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name        TEXT NOT NULL,
    price       NUMERIC(10,2) NOT NULL,
    attributes  JSONB
);

-- Insert with JSON literal
INSERT INTO products (name, price, attributes)
VALUES ('Wireless Keyboard', 79.99, '{"color": "black", "in_stock": true, "tags": ["electronics", "peripherals"]}');

-- Query inside JSONB using ->> (returns text)
SELECT name, attributes->>'color'
FROM products
WHERE attributes @> '{"in_stock": true}';

-- Index a specific JSONB path for fast lookups
CREATE INDEX ON products ((attributes->>'category'));

-- Full-document GIN index for containment queries
CREATE INDEX ON products USING GIN (attributes);

MySQL 5.7+ also has a JSON type, but it lacks PostgreSQL's JSONB binary format and GIN index support. If you're coming from MySQL, think of JSONB as a significantly more capable version: queryable with containment operators, indexable without generated columns, and faster on read-heavy workloads. See our MySQL vs PostgreSQL comparison for a deeper look at how the two databases handle JSON.

Arrays — A PostgreSQL-Only Feature

PostgreSQL supports native array columns for any built-in data type. There's no MySQL equivalent. Arrays let you store multiple values of the same type in a single column without a junction table. The PostgreSQL documentation confirms that arrays are fully indexable with GIN indexes and support operators like ANY, ALL, containment (@>), and overlap (&&) for efficient filtering.

-- Store tags as a TEXT array
CREATE TABLE articles (
    id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title     TEXT NOT NULL,
    tags      TEXT[],
    scores    INTEGER[]
);

-- Insert with ARRAY constructor
INSERT INTO articles (title, tags)
VALUES ('PostgreSQL Guide', ARRAY['postgresql', 'database', 'sql']);

-- Find articles tagged 'postgresql'
SELECT title FROM articles
WHERE 'postgresql' = ANY(tags);

-- Find articles with both 'database' and 'sql' tags (containment)
SELECT title FROM articles
WHERE tags @> ARRAY['database', 'sql'];

-- GIN index for array membership queries
CREATE INDEX ON articles USING GIN (tags);

Arrays are useful for tags, permission sets, and small bounded value lists. Use them deliberately, though. If you frequently join on array values, or the array can grow without bound, a normalized junction table is the better design. Arrays shine when the set is small, bounded, and queried with ANY, not as a surrogate for a proper relation. We use TEXT[] for tag columns in SQL Designer's internal schema and it works well. The moment we needed to join on those values across tables, we moved them to a relation. Use SQL Designer to sketch the tradeoff between an array column and a junction table before committing.

UUID and Identity Columns

PostgreSQL has a native UUID type that stores a 128-bit value in 16 bytes. That's more efficient than MySQL's CHAR(36) (36 bytes of text) and equivalent to MySQL's BINARY(16) without needing manual conversion functions. Since PostgreSQL 13, gen_random_uuid() is a built-in function that generates version 4 UUIDs with no extension required. The PostgreSQL documentation notes that UUID values are accepted in any standard format: with hyphens, without, or with curly braces.

-- UUID primary key (PostgreSQL 13+, no extension needed)
CREATE TABLE users (
    id         UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    email      VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- GENERATED AS IDENTITY — SQL standard, preferred over SERIAL
CREATE TABLE orders (
    id           BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id      UUID NOT NULL REFERENCES users(id),
    total        NUMERIC(10,2) NOT NULL,
    placed_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- SERIAL (legacy — still works, but creates implicit sequence)
CREATE TABLE events (
    id        SERIAL PRIMARY KEY,
    name      TEXT NOT NULL
);

For auto-incrementing integer primary keys, PostgreSQL offers two approaches. The legacy SERIAL pseudo-type creates an implicit sequence. It works, but the sequence is loosely coupled and can cause surprises during dumps and restores. The modern alternative is GENERATED ALWAYS AS IDENTITY (SQL:2003 standard, available since PostgreSQL 10). It provides identical behavior with explicit sequence ownership and is compatible with standards-based tooling. Prefer it in new schemas. For the MySQL equivalent, see our guide to MySQL data types.

Quick Reference: Common Column Patterns

Translating all of the above into practice, these are the column definitions you'll actually write in day-to-day PostgreSQL schemas. Each pattern reflects the type guidance covered in this guide. When you're unsure whether to use UUID or BIGINT for a primary key, or TIMESTAMPTZ vs plain TIMESTAMP, this list is a good starting point.

• Auto-increment primary key (modern): BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
• Auto-increment primary key (legacy): BIGSERIAL PRIMARY KEY
• UUID primary key (PG 13+): UUID DEFAULT gen_random_uuid() PRIMARY KEY
• Email address: VARCHAR(255) NOT NULL UNIQUE
• Password hash: TEXT NOT NULL
• URL slug: TEXT NOT NULL
• Price / monetary value: NUMERIC(10, 2) NOT NULL
• Boolean flag: BOOLEAN NOT NULL DEFAULT false
• Audit timestamp (UTC-aware): TIMESTAMPTZ NOT NULL DEFAULT NOW()
• Long-form text content: TEXT
• JSON metadata: JSONB
• Tags / string list: TEXT[]
• Integer foreign key: BIGINT NOT NULL REFERENCES other_table(id)
• UUID foreign key: UUID NOT NULL REFERENCES other_table(id)
• Status / enum: TEXT NOT NULL CHECK (status IN ('active', 'inactive', 'pending'))