PostgreSQL Data Types

Choose the right data type for each column: integers, text, booleans, dates, and more.

Overview

Choosing the correct data type for each column is one of the most important database design decisions. The right type ensures data integrity (the database rejects invalid values automatically), optimises storage, and enables type-specific operations. PostgreSQL has one of the richest type systems of any relational database including numeric types, text, dates, booleans, JSON, arrays, UUIDs, and geometric types. The most commonly used types fall into four groups: numeric (INTEGER, BIGINT, NUMERIC, REAL), textual (VARCHAR, TEXT, CHAR), date/time (DATE, TIMESTAMP, INTERVAL), and boolean (BOOLEAN). PostgreSQL also provides the serial pseudo-types (SERIAL, BIGSERIAL) as shorthand for auto-incrementing integer columns backed by a sequence. When in doubt, prefer TEXT over VARCHAR(n) for strings (PostgreSQL stores them identically and TEXT has no arbitrary length limit), NUMERIC over FLOAT for money (exact arithmetic), and TIMESTAMPTZ over TIMESTAMP (stores time zone offset, avoiding ambiguity).

Key Points

•INTEGER (4 bytes, ±2 billion) for most counts; BIGINT (8 bytes) for very large numbers
•NUMERIC(precision, scale) for exact decimals use for money, never FLOAT
•TEXT for variable-length strings; VARCHAR(n) is identical but length-limited
•BOOLEAN stores TRUE/FALSE/NULL; accepts 't', 'true', 'yes', '1' as input
•DATE for date only; TIMESTAMP for date+time; TIMESTAMPTZ includes time zone offset
•UUID is a 128-bit unique identifier great for distributed primary keys

Examples

Common column types in a table

CREATE TABLE employees (
  id          SERIAL PRIMARY KEY,
  name        TEXT          NOT NULL,
  email       VARCHAR(255)  UNIQUE,
  salary      NUMERIC(10,2) NOT NULL,
  hired_at    DATE          NOT NULL,
  is_active   BOOLEAN       DEFAULT TRUE,
  notes       TEXT
);

Numeric type comparison

-- INTEGER: small whole numbers
SELECT 2147483647::INTEGER;   -- max int4

-- BIGINT: large whole numbers
SELECT 9223372036854775807::BIGINT;  -- max int8

-- NUMERIC: exact decimal (money, measurements)
SELECT 123456.789::NUMERIC(10,3);

-- REAL/DOUBLE PRECISION: approximate (avoid for money)
SELECT 0.1::REAL + 0.2::REAL;  -- may not equal 0.3 exactly

Date and time types

-- DATE: stores only the date
SELECT '2024-06-15'::DATE;

-- TIMESTAMP: no time zone
SELECT '2024-06-15 14:30:00'::TIMESTAMP;

-- TIMESTAMPTZ: with time zone (recommended)
SELECT NOW()::TIMESTAMPTZ;

-- INTERVAL: a duration
SELECT INTERVAL '2 hours 30 minutes';

Common Mistakes

Using FLOAT or DOUBLE PRECISION for monetary values floating point arithmetic introduces rounding errors. Always use NUMERIC for money.
Using CHAR(n) when you want fixed-length strings CHAR pads with spaces, which can cause surprising comparison results. TEXT or VARCHAR are almost always better.

PostgreSQL Data Types

Overview

Key Points

Examples

Common Mistakes

Exercises

Knowledge Check

Which data type should you use for storing monetary amounts that require exact arithmetic?