Understanding PostgreSQL Data Types

Key Takeaways

1. PostgreSQL offers a wide range of data types to handle diverse data formats.
2. Proper selection of data types improves database performance and integrity.
3. PostgreSQL supports advanced types like arrays, JSON, and range types for complex data modeling.

PostgreSQL offers a comprehensive suite of data types, enabling developers to store and manipulate diverse forms of data efficiently. Selecting the appropriate data type is crucial for ensuring data integrity, optimizing storage, and enhancing query performance. This article delves into the various data types available in PostgreSQL, providing insights into their usage and benefits.

Numeric Types

Numeric types in PostgreSQL are used to store numbers, both integers and floating-point numbers.

• Integer Types:

  • smallint: 2-byte integer, range from -32,768 to 32,767.
  • integer or int: 4-byte integer, range from -2,147,483,648 to 2,147,483,647.
  • bigint: 8-byte integer, range from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.

• Serial Types (auto-incrementing integers):

  • smallserial: 2-byte auto-incrementing integer.
  • serial: 4-byte auto-incrementing integer.
  • bigserial: 8-byte auto-incrementing integer.

• Floating-Point Types:

  • real: 4-byte single-precision floating-point number.
  • double precision: 8-byte double-precision floating-point number.

• Arbitrary Precision Types:

  • numeric or decimal: User-specified precision, suitable for exact numeric values like monetary amounts.

Monetary Type

• money: Stores currency amounts with a fixed fractional precision. It's an 8-byte type suitable for financial calculations.

Character Types

Character types are used to store text strings.

• char(n): Fixed-length character type, space-padded.
• varchar(n): Variable-length character type with a limit.
• text: Variable-length character type with no specific limit.

Binary Data Types

• bytea: Stores binary strings, allowing storage of binary data such as images or files.

Date/Time Types

PostgreSQL provides several types for handling date and time values.

• date: Stores calendar dates (year, month, day).
• time [ (p) ] [ without time zone ]: Stores time of day values.
• time [ (p) ] with time zone: Stores time of day values with time zone.
• timestamp [ (p) ] [ without time zone ]: Stores both date and time.
• timestamp [ (p) ] with time zone: Stores both date and time with time zone.
• interval: Represents a span of time.

Boolean Type

• boolean: Stores logical Boolean values: true, false, or null.

Enumerated Types

Enumerated types (enum) allow the creation of a static, ordered set of values. Useful for columns that should only contain a specific set of values, such as days of the week or status codes.

Example:

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');

Geometric Types

PostgreSQL includes types for representing two-dimensional spatial objects.

• point: A geometric pair of numbers.
• line: Infinite line.
• lseg: Line segment.
• box: Rectangular box.
• path: Closed or open path.
• polygon: Polygon.
• circle: Circle.

Network Address Types

These types are designed to store network-related information.

• cidr: IPv4 or IPv6 network.
• inet: IPv4 or IPv6 host address.
• macaddr: MAC address.

Bit String Types

Used to store bit masks.

• bit(n): Fixed-length bit string.
• bit varying(n): Variable-length bit string.

Text Search Types

PostgreSQL provides types to support full-text search capabilities.

• tsvector: A document in a form optimized for text search.
• tsquery: A text query.

UUID Type

• uuid: Stores Universally Unique Identifiers, useful for identifiers that need to be unique across space and time.

XML Type

• xml: Stores XML data, allowing for storage and querying of XML documents.

JSON Types

PostgreSQL offers robust support for JSON data.

• json: Stores JSON data as text.
• jsonb: Stores JSON data in a binary format, allowing for efficient processing and indexing.

Arrays

PostgreSQL allows columns to be defined as arrays of any built-in or user-defined base type.

Example:

CREATE TABLE products (
    name text,
    tags text[]
);

Composite Types

Composite types allow the creation of a structure with multiple fields.

Example:

CREATE TYPE address AS (
    street text,
    city text,
    zip_code text
);

Range Types

Range types represent a range of values of some element type.

Built-in range types include:

• int4range: Range of integer.
• numrange: Range of numeric.
• tsrange: Range of timestamp without time zone.
• tstzrange: Range of timestamp with time zone.
• daterange: Range of date.

Domain Types

Domains are data types with optional constraints. They are useful for creating reusable constraints.

Example:

CREATE DOMAIN positive_integer AS integer CHECK (VALUE > 0);

Object Identifier Types

• oid: Object identifier, used internally by PostgreSQL to identify objects.

Pseudo-Types

Pseudo-types are special-purpose types that indicate that a function does not return a standard data type.

Examples include:

• any
• void
• internal

Conclusion

PostgreSQL's rich set of data types provides the flexibility to model complex data structures and enforce data integrity. Understanding and utilizing the appropriate data types is fundamental to designing efficient and reliable database systems.

FAQs

What are the most common numeric types in PostgreSQL?

smallint, integer, bigint, real, double precision, and numeric.

How does jsonb differ from json in PostgreSQL?

jsonb stores JSON in a binary format, making it faster for processing and indexing.

Can I create custom types in PostgreSQL?

Yes, using composite types, enumerated types, or domain types.

We are Leapcell, your top choice for hosting backend projects.

Leapcell is the Next-Gen Serverless Platform for Web Hosting, Async Tasks, and Redis:

Multi-Language Support

• Develop with Node.js, Python, Go, or Rust.

Deploy unlimited projects for free

• pay only for usage — no requests, no charges.

Unbeatable Cost Efficiency

• Pay-as-you-go with no idle charges.
• Example: $25 supports 6.94M requests at a 60ms average response time.

Streamlined Developer Experience

• Intuitive UI for effortless setup.
• Fully automated CI/CD pipelines and GitOps integration.
• Real-time metrics and logging for actionable insights.

Effortless Scalability and High Performance

• Auto-scaling to handle high concurrency with ease.
• Zero operational overhead — just focus on building.

Explore more in the Documentation!

Follow us on X: @LeapcellHQ