What is the difference between text and varchar in PostgreSQL?

In PostgreSQL, text and varchar are stored the same way and perform identically. varchar(n) simply adds a maximum-length check. Use text by default, and varchar(n) only when a length limit is a genuine business rule. Unlike some databases, varchar gives no speed advantage here.

Should I use timestamp or timestamptz?

Almost always timestamptz (timestamp with time zone). It stores an unambiguous UTC instant and converts to the session time zone on display, which avoids time-zone bugs. Plain timestamp stores a wall-clock value with no zone information and is easy to misinterpret.

What data type should I use for money?

Use numeric (also called decimal), for example numeric(10,2). It stores values exactly, so cents never drift. Never use float or real for money, because binary floating point cannot represent many decimal values precisely and the rounding errors accumulate.

Beginner

PostgreSQL Data Types: Choosing the Right Column Type

The type you pick for a column decides what it can store, how it sorts, and how fast it queries. A practical tour of numbers, text, dates, booleans, JSON, and the choices that bite later.

A column’s type is a contract: it decides what values are allowed, how they sort and compare, what operations work, and how efficiently the column stores and indexes. Choosing well up front saves painful migrations later. This guide covers the PostgreSQL types you will actually use. Keep the SQL cheat sheet open as you read.

Why Types Matter

Store a price as text and you cannot do math on it, it sorts "100" before "20", and an index on it is useless for ranges. The right type makes correct behaviour automatic. PostgreSQL is strict about types, which catches bugs early instead of letting bad data in.

Numbers

Use integer types (smallint, integer, bigint) for whole numbers like counts and ids. Use numeric (also called decimal) for money and anything where exact precision matters. Avoid float/real for currency — binary floating point cannot represent values like 0.10 exactly.

quantity int           NOT NULL,  -- whole number
price    numeric(10,2) NOT NULL,  -- exact: up to 10 digits, 2 after the point
rating   real                     -- approximate float, fine for non-money

Text

In PostgreSQL, text and varchar are stored identically and perform the same — text has no length limit and is the simplest choice. Use varchar(n) only when you genuinely need to enforce a maximum length. Plain char(n) is almost never the right call; it pads with spaces.

name text        NOT NULL,  -- no length limit, idiomatic
code varchar(8)              -- only when a real max length applies

Dates & Times

Use date for a calendar day, and timestamptz (timestamp with time zone) for a moment in time. Prefer timestamptz over plain timestamp almost always — it stores an unambiguous instant and converts to the session time zone on display, which avoids a whole class of time-zone bugs.

birthday   date        NOT NULL,
created_at timestamptz NOT NULL DEFAULT now()

Boolean & UUID

Use boolean for true/false flags rather than an integer 0/1 or a text 'Y'/'N' — it is clearer and self-documenting. Use uuid for globally unique identifiers when you need ids that are unguessable or generated outside the database.

is_active boolean NOT NULL DEFAULT true,
public_id uuid    NOT NULL DEFAULT gen_random_uuid()

JSON & Arrays

PostgreSQL can store semi-structured data with jsonb (binary JSON, indexable and faster than plain json) and even native arrays. They are powerful for flexible attributes — but if you find yourself querying deep into JSON constantly, that data probably wants real columns or tables instead.

metadata jsonb,                -- queryable, indexable JSON
tags     text[] NOT NULL DEFAULT '{}'  -- a real array of text

-- Query into jsonb
SELECT * FROM events WHERE metadata->>'source' = 'mobile';

Common Mistakes & Tips

Storing money as float or real — use numeric for exact arithmetic
Storing numbers or dates as text, which breaks sorting, math, and indexes
Using plain timestamp instead of timestamptz and inheriting time-zone bugs
Reaching for char(n) — it pads with spaces; prefer text or varchar
Dumping everything into jsonb instead of modelling data you actually query

Picking the right type is a small decision with long consequences. Practice on realistic, well-typed datasets to see how numeric, text, and timestamptz behave in real queries.