PostgreSQL Data Types

PostgreSQL has one of the richest type systems of any relational database. Beyond the standard SQL numeric, text, and date types, it offers native UUID, JSON/JSONB, array, and range types, plus the ability to define your own custom types. Choosing the right type up front avoids painful migrations and subtle correctness bugs later, especially around money and time.

The two areas where beginners most often pick the wrong type are numeric precision (using floating-point types for currency) and timestamps (ignoring time zones). Both mistakes are easy to avoid once you understand what PostgreSQL actually offers.

Why it matters

PostgreSQL offers a richer set of types than most databases, and the right choice prevents whole categories of bugs: money that drifts by a paisa, timestamps that are wrong after a server moves time zone, identifiers that collide. Changing the type of a column on a large table later is slow and risky.

Text and Character Types

PostgreSQL provides CHAR(n) (fixed-length, blank-padded), VARCHAR(n) (variable-length with a limit), and TEXT (variable-length, unlimited). Unlike some databases, PostgreSQL has no meaningful performance difference between these — internally they use the same storage. Because of this, idiomatic PostgreSQL style favors TEXT for almost everything, adding a CHECK constraint if you truly need to cap a length, rather than reaching for VARCHAR(n) out of habit.

Numeric Types: NUMERIC vs. FLOAT

INTEGER and BIGINT handle whole numbers. For decimals, PostgreSQL gives you two fundamentally different families: REAL/DOUBLE PRECISION (binary floating-point, fast but inexact) and NUMERIC(precision, scale) (exact, arbitrary-precision decimal). This distinction matters enormously for money: floating-point types cannot represent values like 0.10 exactly in binary, so repeated arithmetic accumulates rounding error. NUMERIC stores digits exactly as specified, which is why every serious application uses NUMERIC(10,2) or similar for prices, balances, and any financial calculation — never FLOAT or REAL.

Date/Time, Boolean, and UUID

PostgreSQL distinguishes TIMESTAMP (no time zone, stores a naive wall-clock value) from TIMESTAMPTZ (stores the instant in UTC internally and converts to the session's time zone on display). For almost any application that could ever run across time zones, TIMESTAMPTZ is the correct default — it avoids an entire class of "which time zone was this in" bugs. BOOLEAN is a true first-class type (TRUE/FALSE/NULL), not an integer alias. UUID is a native 128-bit type, commonly used for primary keys in distributed systems or when IDs must not be sequentially guessable; generate values with gen_random_uuid() (from the built-in pgcrypto-free pgcrypto or, in modern PostgreSQL, the core uuid-ossp/pgcrypto functions).

Examples

CREATE TABLE orders (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    customer    TEXT NOT NULL,
    total_price NUMERIC(10,2) NOT NULL,
    is_paid     BOOLEAN NOT NULL DEFAULT FALSE,
    placed_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

INSERT INTO orders (customer, total_price)
VALUES ('Ravi Shah', 149.99);

SELECT customer, total_price, is_paid, placed_at FROM orders;

INSERT 0 1
   customer   | total_price | is_paid |           placed_at
--------------+-------------+---------+-------------------------------
 Ravi Shah    |      149.99 | f       | 2026-09-21 10:32:07.481212+00

SELECT
    0.1::float8 + 0.2::float8            AS float_sum,
    0.1::numeric(4,2) + 0.2::numeric(4,2) AS numeric_sum;

     float_sum      | numeric_sum
---------------------+-------------
 0.30000000000000004 |        0.30

Exercise

This exercise uses PostgreSQL types that SQLite does not have, so it needs a PostgreSQL server. Create a payments table with: a UUID primary key that the database generates; an amount stored exactly, with two decimal places; a required text currency; a boolean refunded that defaults to false; and a paid_at timestamp with time zone that defaults to the current time. Then insert a payment giving only the amount and currency, and return the whole row.

Expected result: one row, with a generated id, refunded false and paid_at set to the time of the insert.

-- TODO: create the payments table

-- TODO: insert amount 499.99 in INR and return the inserted row

CREATE TABLE payments (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  amount NUMERIC(12, 2) NOT NULL,
  currency TEXT NOT NULL,
  refunded BOOLEAN NOT NULL DEFAULT false,
  paid_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

INSERT INTO payments (amount, currency)
VALUES (499.99, 'INR')
RETURNING *;

Quick quiz

Interview questions