PostgreSQL Data Types

Every column in a PostgreSQL table must have a data type. A data type tells PostgreSQL what kind of value a column will hold — a number, a piece of text, a date, or something else. Choosing the right data type ensures data is stored efficiently, validated automatically, and queried accurately.

Why Data Types Matter

Imagine storing someone's age as text instead of a number. PostgreSQL would not be able to perform math on it, like calculating an average age. Using the correct data type prevents this kind of problem and also saves storage space by using the most efficient format for each value.

Categories of Data Types

PostgreSQL data types fall into several categories: numeric, character (text), boolean, date and time, and a few special types. Each is covered below.

Numeric Data Types

Numeric types store numbers. The choice depends on whether the number is a whole number or has a decimal, and how large it can get.

Integer Types

Auto-Incrementing Integer Types

Decimal and Floating-Point Types

For financial data, always use NUMERIC or DECIMAL. Floating-point types can introduce tiny rounding errors that are unacceptable in money calculations.

Character (Text) Data Types

These types store text — letters, symbols, words, and sentences.

In PostgreSQL, TEXT and VARCHAR perform identically in terms of speed. Use VARCHAR(n) when a maximum length should be enforced, and TEXT when there is no reasonable limit.

Boolean Data Type

The BOOLEAN type stores one of two values: true or false. It is useful for flags and yes/no fields.

is_active BOOLEAN

Accepted input values for true: TRUE, 't', 'yes', 'on', 1

Accepted input values for false: FALSE, 'f', 'no', 'off', 0

Date and Time Data Types

PostgreSQL has rich support for storing dates and times.

For applications that span multiple time zones, use TIMESTAMPTZ. It stores the time in UTC internally and converts to the local timezone when displaying.

UUID Data Type

UUID (Universally Unique Identifier) stores a 128-bit value that is globally unique. It looks like this: a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11. UUIDs are commonly used as primary keys in distributed systems where multiple databases must generate IDs without conflicts.

Array Data Type

PostgreSQL allows a column to store an array (a list) of values of any type. For example, a column that stores multiple phone numbers for a person:

phone_numbers TEXT[]

An array value would look like: {'9876543210', '9123456789'}

JSON and JSONB Data Types

PostgreSQL can store JSON (JavaScript Object Notation) data directly in a column.

• JSON — stores the raw JSON text exactly as entered
• JSONB — stores JSON in a binary format that is faster to query and supports indexing

For most use cases, JSONB is preferred. JSON and JSONB are covered in detail in a later topic.

NULL — The Absence of a Value

NULL is not a data type — it is a special marker meaning "no value" or "unknown." Any column of any type can contain NULL unless restricted by a NOT NULL constraint. NULL is different from zero or an empty string. It literally means the value does not exist.

Example: Choosing Data Types for a Products Table

Consider a table that stores product information:

CREATE TABLE products (
    product_id   SERIAL,
    product_name VARCHAR(100),
    description  TEXT,
    price        NUMERIC(10, 2),
    in_stock     BOOLEAN,
    created_on   DATE
);

Breaking this down:

• SERIAL — auto-generates a unique product ID
• VARCHAR(100) — limits the product name to 100 characters
• TEXT — allows an unlimited description
• NUMERIC(10, 2) — stores prices like 9999999.99 (10 digits, 2 decimal places)
• BOOLEAN — indicates whether the product is available
• DATE — stores the date the product was added

Key Points

• Every column requires a data type that defines what kind of data it stores.
• Use INTEGER or SERIAL for whole numbers, NUMERIC for exact decimals like money.
• Use VARCHAR(n) for text with a length limit, TEXT for unlimited text.
• Use TIMESTAMPTZ for timestamps when timezone awareness is needed.
• NULL means a value is absent — it is not the same as zero or empty text.
• Choosing the right data type improves storage efficiency, query speed, and data accuracy.