Back to all posts

PostgreSQL: data types

By on Nov 26, 2025

Tags:

Understanding data types is foundational for optimizing database design, ensuring data integrity, and enhancing application performance.

Choosing the Right Data Type

Selecting the proper data types is crucial to:

  • Optimize storage and improve query performance
  • Maintain data accuracy and integrity
  • Facilitate effective indexing and data retrieval

For example, use numeric types for precise calculations, JSONB for flexible schema needs, and enums for controlled vocabularies.

Core Categories of PostgreSQL Data Types

1. Numeric Types

PostgreSQL supports several numeric types to cover various numerical needs:

    • Integer types: smallint (2 bytes), integer (4 bytes), bigint (8 bytes)
    • Serial types: smallserial, serial, bigintserial for auto-incrementing integers
    • Floating-point types: real (4 bytes), double precision (8 bytes)
    • Exact numeric: numeric and decimal types with user-defined precision useful for financial calculations requiring exactness without rounding errors

    2. Character Types

    For textual data, PostgreSQL provides:

      • char(n): Fixed-length character strings, blank-padded
      • varchar(n): Variable-length strings with limit
      • text: Variable unlimited length strings, suitable for large text content

      3. Boolean and Enumerated Types

      • Boolean (bool): Stores true, false, or unknown values, widely used in conditional data.
      • Enumerated types (enum): Allow for defining a column with a predefined set of string values, useful for categorical data.

      4. Temporal Types

      Managing dates and times is essential:

        • date: Calendar date (year, month, day)
        • time [without/with time zone]: Time of day
        • timestamp [without/with time zone]: Combination of date and time
        • interval: Represents time durations

        5. JSON and XML Types
        PostgreSQL excels in semi-structured data:

          • json stores JSON data as text
          • jsonb stores JSON in a decomposed binary format for faster access and query operations
          • xml stores XML data structure

          6. Binary and UUID Types

          • bytea: Stores binary data like images or files
          • uuid: Stores Universally Unique Identifiers, commonly used for unique keys

          7. Geometric and Network Address Types

          • Geometric types like point, line, circle for spatial data
          • Network address types such as inet, cidr for storing IP addresses, and macaddr for MAC addresses

          Extensibility and Custom Types

          One of PostgreSQL’s standout features is its extensibility. Users can create custom data types or leverage extensions like PostGIS for geospatial data, broadening PostgreSQL’s usability across various specialized fields such as GIS, machine learning, and network management.

          For more detailed notes refer this:
          https://www.postgresql.org/docs/current/datatype.html