Data Types

Understand PostgreSQL's rich type system and choose the right data types for your application.

Overview

PostgreSQL offers a comprehensive set of data types that go beyond basic integers and strings. Choosing the right data type ensures data integrity, optimizes storage, and improves query performance.

Numeric Types

Integer Types

  • smallint: 2-byte integer (-32,768 to 32,767)

  • integer (int): 4-byte integer (-2 billion to 2 billion)

  • bigint: 8-byte integer (very large numbers)

Use cases: counters, IDs, quantities

Decimal Types

  • decimal/numeric: Exact precision for financial calculations

  • real: 4-byte floating-point

  • double precision: 8-byte floating-point

Use cases: prices, measurements, scientific calculations

Floating-Point Types

For approximate numeric values with a large range. Be aware of precision limitations.

Learn more: Integer | Decimal | Floating-Point

Character Types

String Storage

  • character(n) / char(n): Fixed-length, space-padded

  • character varying(n) / varchar(n): Variable-length with limit

  • text: Variable unlimited length (preferred in most cases)

Use cases: names, descriptions, content

Best practice: Use text unless you have a specific reason for length limits.

Learn more: Character Types

Boolean Type

  • boolean / bool: True, false, or null values

Use cases: flags, status indicators, binary choices

Learn more: Boolean

Date & Time Types

Temporal Data

  • date: Calendar date (year, month, day)

  • time: Time of day without timezone

  • timestamp: Date and time without timezone

  • timestamptz: Date and time with timezone (recommended)

  • interval: Time span

Use cases: events, schedules, durations, analytics

Best practice: Always use timestamptz for timestamps to avoid timezone issues.

Learn more: Date and Time

JSON Types

Document Storage

  • json: Text-based JSON storage

  • jsonb: Binary JSON with indexing (recommended)

JSONB advantages:

  • Faster processing

  • Supports indexing

  • More efficient storage

  • Can't preserve key order or whitespace

Use cases: flexible schemas, API responses, nested data, document storage

Learn more: JSON

UUID Type

  • uuid: Universally Unique Identifier (128-bit)

Use cases: distributed systems, public IDs, security-sensitive identifiers

Best practice: Use UUID v4 for random IDs or UUID v7 for time-ordered IDs.

Learn more: UUID

Array Types

PostgreSQL supports arrays of any built-in or user-defined type:

  • integer[]: Array of integers

  • text[]: Array of text values

  • jsonb[]: Array of JSONB documents

Use cases: tags, multi-value attributes, time series

Learn more: Array

Full-Text Search Types

Text Search

  • tsvector: Processed document for full-text search

  • tsquery: Full-text search query

Use cases: search functionality, content indexing

Learn more: tsvector

Specialized Types

Network Types

  • inet: IPv4 or IPv6 host address

  • cidr: IPv4 or IPv6 network

  • macaddr: MAC address

Geometric Types

  • point: Point in 2D space

  • line: Infinite line

  • circle: Circle

  • polygon: Closed path

Binary Types

  • bytea: Binary data ("byte array")

Range Types

  • int4range: Range of integers

  • tsrange: Range of timestamps

  • daterange: Range of dates

Choosing the Right Type

Considerations

  1. Storage Efficiency: Smaller types use less disk space

  2. Performance: Some types are faster for specific operations

  3. Constraints: Types enforce data validity automatically

  4. Indexing: Some types support specialized indexes

  5. Application Compatibility: Match your programming language types

Common Decisions

  • IDs: Use bigint or uuid for primary keys

  • Money: Use decimal/numeric (never float!)

  • Timestamps: Always use timestamptz

  • Text: Use text unless you need char/varchar

  • Flags: Use boolean, not integer 0/1

  • JSON: Use jsonb for querying, json only if order matters

Type Conversions

PostgreSQL allows casting between compatible types:

-- Explicit cast
SELECT CAST('123' AS integer);
SELECT '123'::integer;

-- Implicit conversion (be careful!)
SELECT '2024-01-01'::date + 7;  -- Adds 7 days

User-Defined Types

You can create custom types:

  • ENUM: Enumerated types

  • COMPOSITE: Row types

  • DOMAIN: Custom constraints on existing types

Best Practices

  1. Choose the smallest sufficient type: Saves space and improves performance

  2. Use constraints: Add CHECK constraints for additional validation

  3. Prefer standard types: Use PostgreSQL types over application-level strings

  4. Consider future needs: Choose types that allow growth (e.g., bigint vs int)

  5. Use JSONB for flexibility: When schema changes frequently

  6. Always use timestamptz: For timestamps across timezones

  7. Avoid CHAR: Use TEXT or VARCHAR instead

Getting Started

  1. Review the Data Types Introduction

  2. Understand JSON types for flexible schemas

  3. Master Date and Time types for temporal data

  4. Learn about Arrays for multi-value columns

Performance Tips

  • Proper indexing: Different types support different index types (B-tree, GIN, GiST)

  • Type-specific operations: Use appropriate operators for each type

  • Avoid unnecessary casts: Can prevent index usage

  • JSONB for queries: Much faster than JSON for searching

Understanding data types is fundamental to effective database design and optimal performance.

Previousarray_to_jsonNextIntroduction

Last updated