Data Types Basics

10 examples to get you started with PostgreSQL data types - 7 basic and 3 intermediate.

Prerequisites

Connected to PostgreSQL 18 with CREATE privilege on a scratch schema.

# Local dev with Docker (PostgreSQL 18)
docker run -d --name pg18 -e POSTGRES_PASSWORD=dev -p 5432:5432 postgres:18
psql postgres://postgres:dev@localhost:5432/postgres

Basic Examples

1. Integer Family

bigint for IDs; int for counts; smallint for enums-as-int.

CREATE TABLE app.counts (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  score int NOT NULL CHECK (score BETWEEN 0 AND 100)
);

• bigint avoids int overflow on snowflake IDs
• CHECK enforces range without app-only validation
• Smaller types narrow indexes

2. Text vs varchar

text has no performance penalty vs varchar(n) in PostgreSQL.

CREATE TABLE app.labels (
  code text NOT NULL UNIQUE,
  title text NOT NULL
);

• Use varchar(n) when standard mandates max length
• text is default for arbitrary strings
• Collation affects sort order

3. Boolean

Three-valued if nullable; prefer NOT NULL with default.

ALTER TABLE app.customers ADD COLUMN is_active boolean NOT NULL DEFAULT true;

• Avoid boolean tri-state unless NULL means unknown
• Partial indexes often filter is_active = true

4. Numeric for Money

numeric(p,s) for exact decimal arithmetic.

CREATE TABLE app.payments (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  amount numeric(12,2) NOT NULL CHECK (amount >= 0)
);

• Never float for currency
• Scale fixed in type definition
• SUM and AVG stay exact

5. timestamptz

Store instants in UTC.

CREATE TABLE app.events (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  occurred_at timestamptz NOT NULL DEFAULT now()
);

• Session TimeZone affects display only
• Avoid timestamp without time zone for real events
• Range queries benefit from btree on occurred_at

6. UUID Keys

uuid type with gen_random_uuid().

CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE TABLE app.tokens (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid()
);

• Random UUIDs fragment indexes slightly
• Consider uuidv7 at app layer for time order
• Smaller than text UUID strings

7. jsonb Documents

Binary JSON with indexing operators.

CREATE TABLE app.profiles (
  user_id bigint PRIMARY KEY,
  attrs jsonb NOT NULL DEFAULT '{}'::jsonb
);
INSERT INTO app.profiles VALUES (1, '{"tier":"pro"}');

• jsonb removes duplicate keys and decomposes
• GIN indexes accelerate containment queries
• Do not mirror all relational fields in JSON

Intermediate Examples

8. Enum Type

Closed set of labels stored as OIDs.

CREATE TYPE app.plan_tier AS ENUM ('free','pro','enterprise');
ALTER TABLE app.customers ADD COLUMN tier app.plan_tier NOT NULL DEFAULT 'free';

• Fast comparisons and readable output
• Adding values needs migration discipline
• Lookup tables more flexible for frequent changes

9. Array Column

Postgres arrays for tags (use carefully).

ALTER TABLE app.customers ADD COLUMN tags text[] NOT NULL DEFAULT '{}';
CREATE INDEX customers_tags_gin ON app.customers USING gin (tags);

• GIN supports @> containment
• Avoid unbounded array growth
• Normalize when elements become entities

10. Domain Wrapper

Reusable CHECK on a base type.

CREATE DOMAIN app.email AS text
  CHECK (VALUE ~* '^[^@]+@[^@]+\.[^@]+$');
ALTER TABLE app.customers ALTER COLUMN email TYPE app.email USING email::app.email;

• Single definition for repeated rules
• Errors reference domain name
• Works with NOT NULL and UNIQUE

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.