Data Types

Every column in a PostgreSQL table has a data type. The data type tells PostgreSQL what kind of data to expect and how to store it. Choosing the right data type matters — it affects storage space, performance, and what operations you can perform on the data.

Numeric Types

For numbers, PostgreSQL gives you several options:

• INTEGER — Whole numbers from -2.1 billion to 2.1 billion. Use this for most counting and ID scenarios.
• BIGINT — Much larger whole numbers. Use when INTEGER is not enough.
• SMALLINT — Small whole numbers up to 32,767. Saves space when you know the range is small.
• SERIAL — Auto-incrementing INTEGER. Perfect for primary keys.
• BIGSERIAL — Auto-incrementing BIGINT for when you expect a lot of rows.
• NUMERIC(precision, scale) — Exact decimal numbers. Use for money and precise calculations.
• FLOAT and DOUBLE PRECISION — Floating point numbers. Good for scientific calculations but avoid for money.

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  price NUMERIC(10, 2),
  quantity INTEGER,
  weight FLOAT
);

For money, always use NUMERIC. Floating point types can have rounding errors that will drive you crazy when dealing with currency.

Text Types

For text data:

• VARCHAR(n) — Variable-length text with a maximum length. Use when you have a known limit.
• TEXT — Variable-length text with no practical limit. Use when you do not know the maximum length.
• CHAR(n) — Fixed-length text, padded with spaces. Rarely used in practice.

CREATE TABLE articles (
  id SERIAL PRIMARY KEY,
  title VARCHAR(200),
  slug VARCHAR(200) UNIQUE,
  body TEXT,
  status CHAR(1)
);

In practice, most developers use TEXT for everything unless they have a specific reason to limit the length. PostgreSQL does not charge extra for longer text.

Date and Time Types

PostgreSQL has excellent date and time support:

• DATE — Calendar date (year, month, day) without time
• TIME — Time of day without date
• TIMESTAMP — Date and time combined
• TIMESTAMPTZ — Timestamp with timezone information. This is usually what you want.
• INTERVAL — A duration of time (like 3 hours, 20 minutes)

CREATE TABLE events (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  event_date DATE,
  start_time TIME,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

Always use TIMESTAMPTZ instead of TIMESTAMP. It stores the timezone and converts correctly when querying across different timezones.

Boolean Type

For true/false values:

CREATE TABLE tasks (
  id SERIAL PRIMARY KEY,
  title VARCHAR(200),
  is_completed BOOLEAN DEFAULT FALSE
);

You can insert values using TRUE, FALSE, or abbreviations 't' and 'f'.

UUID Type

PostgreSQL has a native UUID type for universally unique identifiers:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  username VARCHAR(50) NOT NULL
);

UUIDs are great for distributed systems where you need to generate IDs without coordinating between servers.