PostgreSQL Data Types

Objectives

By the end of this lesson, you should be able to:

• Choose an appropriate PostgreSQL data type for a given piece of data
• Explain the difference between VARCHAR(n) and TEXT
• Explain why NUMERIC is used for money instead of a floating-point type

💡 Why this matters: Every column in every table you’ll ever create needs a data type. Pick the right one, and PostgreSQL enforces it for you automatically, pick the wrong one, and bad data gets in quietly.

⚠️ A note on verification: every example and error message in this lesson was run against a real, live PostgreSQL 18 database.

Text Types

first_name VARCHAR(50)   -- text, with a maximum length
notes TEXT                -- text, no length limit

VARCHAR(n) stores text up to n characters, and PostgreSQL enforces that limit automatically. For example, a table with code VARCHAR(5) rejects anything longer:

INSERT INTO short_codes (code) VALUES ('TOOLONGVALUE');

ERROR: value too long for type character varying(5)

TEXT stores text of any length, no maximum. In PostgreSQL specifically, TEXT and VARCHAR perform essentially the same, so the choice is about intent: use VARCHAR(n) when there’s a real, meaningful limit (a name, a short code), and TEXT for genuinely open-ended content (notes, a long description).

Numbers

id INTEGER
salary NUMERIC(10,2)

INTEGER stores whole numbers, no decimal point. NUMERIC(precision, scale) stores exact decimal numbers, precision is the total number of digits, scale is how many of those are after the decimal point. NUMERIC(10,2) allows up to 10 total digits, 2 after the decimal.

NUMERIC is the correct choice for money specifically, because it’s exact. A floating-point type (REAL or DOUBLE PRECISION) can introduce tiny rounding errors, entirely fine for scientific measurements, a real problem when a salary needs to be exactly 78000.00, not 78000.00000000001.

Boolean

is_active BOOLEAN NOT NULL DEFAULT true

BOOLEAN stores true or false (and NULL, meaning “unknown,” unless the column is NOT NULL).

Dates and Times

hire_date DATE                              -- just a calendar date
created_at TIMESTAMP NOT NULL DEFAULT now()  -- date and time together

DATE stores a calendar date with no time component. TIMESTAMP stores both a date and a time. now() and CURRENT_DATE are built-in functions returning the current moment, commonly used as defaults, covered further in the next lesson.

Putting It Together

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    department VARCHAR(50),
    salary NUMERIC(10,2) NOT NULL,
    hire_date DATE NOT NULL DEFAULT CURRENT_DATE,
    is_active BOOLEAN NOT NULL DEFAULT true
);

This is the employees table used throughout the rest of this module, one column from almost every type category covered above. UNIQUE, NOT NULL, PRIMARY KEY, and DEFAULT are all covered in their own lessons next, for now, focus on the types themselves: SERIAL (an auto-incrementing integer, its own lesson shortly), VARCHAR(n), NUMERIC(10,2), DATE, and BOOLEAN.

Try It

For each piece of data below, pick the PostgreSQL type you’d use and explain why:

1. A department name, always short.
2. An employee’s full biography, could be a sentence or several paragraphs.
3. An hourly wage like 24.50.
4. Whether an employee is currently active.
5. The exact date an employee was hired.

Recap

• VARCHAR(n) limits text length and PostgreSQL enforces it, TEXT allows unlimited length.
• NUMERIC(precision, scale) stores exact decimal numbers, the right choice for money.
• BOOLEAN, DATE, and TIMESTAMP store true/false values, dates, and date-plus-time values respectively.

Next lesson: NULL and DEFAULT values, what “no value” means and how to fill one in automatically.