Choosing the Right Data Types

Before you add NOT NULL, UNIQUE, or CHECK, every column already has a rule: its data type. A column declared as INTEGER does not accept arbitrary text. A column declared as DATE does not accept a random sentence.

That is why data types are the first constraint in a schema.

Type equals domain

A domain is the set of values a column is allowed to hold. Choosing a type chooses the broad domain before any other constraint narrows it.

If quantity is INTEGER, PostgreSQL rejects two. If ordered_at is TIMESTAMPTZ, PostgreSQL stores a timestamp rather than a vague text label. The type makes many invalid values unrepresentable.

Watch a type reject bad input

This table says quantity is an integer. PostgreSQL will not store text that cannot be converted into an integer.

The CHECK adds the rule that quantity must be positive, but the data type catches an even earlier problem: the value must be an integer at all.

Choosing common types

You do not need every PostgreSQL type on day one. Start with the common modeling choices.

• Use TEXT for ordinary strings. VARCHAR(n) is useful when the length limit is a real business rule, not just habit.
• Use INTEGER for normal whole numbers, BIGINT for very large whole numbers.
• Use NUMERIC for exact decimal values such as money.
• Use BOOLEAN for true/false facts.
• Use DATE for a calendar day with no time of day.
• Use TIMESTAMP or TIMESTAMPTZ for a date plus time.

Text vs. VARCHAR

For most free-form text in PostgreSQL, TEXT is the simple choice: names, descriptions, titles, notes. Use VARCHAR(20) only when "at most 20 characters" is a true rule of the domain.

A product description probably does not have a meaningful database-level maximum. A two-letter country code does — though for that, you might use CHAR(2) or TEXT CHECK (length(code) = 2) depending on the design.

Numbers: integer, bigint, numeric

Use whole-number types when fractions do not make sense. A count of items should be INTEGER, not text and not a decimal.

Use BIGINT when the number may grow beyond the normal integer range, such as very large identifiers or counters.

Use NUMERIC when exact decimal arithmetic matters. Money is the classic example.

Why not float for money?

Floating-point types are approximate. They are excellent for scientific measurements, but money usually needs exact decimal behavior.

The exact result is what you want for prices, invoices, balances, and other financial values. Floating point may display surprising tiny rounding differences because it stores approximate binary fractions.

Quick check

QuestionSelect one

Why is NUMERIC usually a better choice than floating point for money?

NUMERIC stores only whole numbers.

NUMERIC stores exact decimal values, while floating point is approximate.

Floating point cannot store numbers less than one.

NUMERIC automatically creates invoices.

Boolean and dates

Use BOOLEAN for true/false facts: is_active, is_public, email_verified. Avoid storing these as text like 'yes' and 'no' unless the values are genuinely more varied than a boolean.

Use DATE when the fact is a day on the calendar: birth date, due date, start date. Use TIMESTAMP when the time of day matters. Use TIMESTAMPTZ when the value represents a real moment that should be understood consistently across time zones, such as created_at.

You do not have to become a time-zone expert here. The design habit is to ask what the value means, then choose the narrowest honest type.

ENUM-like choices

Some columns should hold one value from a small allowed set: order status, user role, product state. There are two common schema designs:

• TEXT plus a CHECK, such as status IN ('draft', 'paid', 'shipped')
• A PostgreSQL ENUM type

For beginner schema design, a CHECK is often easier to change and to see directly in the table definition. A real enum can be useful when the set is stable and reused across many tables.

Types work with constraints

Types are broad constraints. Other constraints narrow the rule.

The strongest designs combine them. A product price might be NUMERIC for exact decimals, NOT NULL because every product needs a price, and CHECK (price >= 0) because negative prices are invalid.

Quick check

QuestionSelect one

A column stores whether an account is active or inactive. Which type usually best expresses that domain?

TEXT.

INTEGER.

BOOLEAN.

DATE.

Check your understanding

QuestionSelect one

Why are data types called the first constraint in a schema?

They are checked only after all other constraints fail.

They define the broad set of values a column can store before other constraints narrow it further.

They replace primary keys and foreign keys.

They are optional labels for documentation.

QuestionSelect one

Which type is usually appropriate for a product quantity that must be a whole number?

TEXT.

NUMERIC with many decimal places.

INTEGER.

BOOLEAN.

QuestionSelect one

A column stores the exact moment an order was created. Which type is usually the strongest choice?

DATE.

TEXT.

TIMESTAMPTZ.

BOOLEAN.

QuestionSelect one

Which design best represents an order status with a small allowed set such as draft, paid, and shipped?

status TEXT with no other rule.

status TEXT CHECK (status IN ('draft', 'paid', 'shipped')).

status INTEGER with no explanation.

status BOOLEAN.