PostgreSQL Data Types

Unlike MongoDB's flexible per-document typing, PostgreSQL is strictly typed at the column level — every column in a table has one fixed data type.

Numeric Types

Type	Description
SMALLINT	2-byte integer (-32,768 to 32,767)
INTEGER / INT	4-byte integer (~±2.1 billion)
BIGINT	8-byte integer, for very large counts
NUMERIC(p, s)	Exact decimal — p total digits, s after the decimal point (e.g. money)
REAL / DOUBLE PRECISION	Floating-point, for approximate values
SERIAL / BIGSERIAL	Auto-incrementing integer, commonly used for primary keys

price NUMERIC(10, 2)  -- up to 99999999.99, exact — never use float for money

Character Types

Type	Description
VARCHAR(n)	Variable-length text, up to n characters
CHAR(n)	Fixed-length text, padded with spaces
TEXT	Variable-length text with no length limit

In practice, TEXT performs the same as VARCHAR in PostgreSQL — use VARCHAR(n) only when you genuinely need to enforce a length limit.

Boolean

is_active BOOLEAN DEFAULT true

Date & Time Types

Type	Description
DATE	Just a calendar date
TIME	Just a time of day
TIMESTAMP	Date + time, no timezone
TIMESTAMPTZ	Date + time, timezone-aware (recommended for almost everything)
INTERVAL	A span of time, e.g. '3 days'

created_at TIMESTAMPTZ DEFAULT now()

UUID

id UUID DEFAULT gen_random_uuid() PRIMARY KEY

JSON & JSONB

metadata JSONB

JSONB stores JSON in a decomposed binary format — it's slightly slower to insert but much faster to query and can be indexed, so prefer it over plain JSON for almost every use case.

Arrays

Any type can be turned into an array by appending [].

tags TEXT[]
scores INTEGER[]

Common Errors & Troubleshooting

• ERROR: numeric field overflow — you exceeded the digit limits of a NUMERIC(p, s) column — increase p (total digits) if the values genuinely need to be larger.
• Losing cents of precision using REAL/DOUBLE PRECISION for money — floating-point types can't represent all decimal values exactly, causing small rounding errors that add up. Always use NUMERIC for currency.
• Comparing a TIMESTAMP and TIMESTAMPTZ column giving surprising results — mixing timezone-aware and timezone-naive columns in the same comparison can silently apply the wrong timezone assumption; keep a consistent type across related columns, and prefer TIMESTAMPTZ throughout.

Key Takeaways

• Postgres columns are strictly typed — you must pick the right type up front, unlike MongoDB's per-document flexibility.
• Use NUMERIC (never floating point) for money and other exact values.
• TIMESTAMPTZ is almost always the right choice over plain TIMESTAMP.
• Prefer JSONB over JSON — it's indexable and faster to query.