TutorialsArena

PostgreSQL Data Types: A Comprehensive Guide

Explore the various data types available in PostgreSQL, from numeric and character types to date/time and geometric types. Choose the right type for your data.

PostgreSQL Data Types: A Comprehensive Guide

Introduction

PostgreSQL, a powerful open-source relational database, supports a wide variety of data types. Choosing the correct data type for each column in your database is crucial for performance, data integrity, and efficient storage. This guide provides an overview of the many data types available in PostgreSQL.

Benefits of Using Appropriate Data Types

  • Improved Performance: Optimized storage and retrieval of data.
  • Data Validation: Prevents incorrect data from being stored.
  • Compact Storage: Efficient use of disk space.
  • Data Consistency: Ensures reliable results for operations on columns of the same type.

Categorization of PostgreSQL Data Types

PostgreSQL data types can be broadly categorized as follows:

  • Numeric
  • Character
  • Date/Time
  • Monetary
  • Binary
  • Boolean
  • Enumerated
  • Geometric
  • Text Search
  • UUID
  • Network Address
  • JSON
  • Bit String
  • XML
  • Range
  • Arrays
  • Composite
  • Object Identifiers
  • Pseudo-types
  • pg_lsn

Detailed Data Type Descriptions

Numeric Data Types

Name Description Storage Size Range
smallint 2-byte integer 2 bytes -32768 to +32767
integer 4-byte integer 4 bytes -2147483648 to +2147483647
bigint 8-byte integer 8 bytes -9223372036854775808 to 9223372036854775807
decimal User-specified precision decimal Variable Up to 131072 digits before decimal; up to 16383 after
numeric User-specified precision decimal (same as decimal) Variable Up to 131072 digits before decimal; up to 16383 after
real 4-byte floating-point 4 bytes Approximately 6 decimal digits precision
double precision 8-byte floating-point 8 bytes Approximately 15 decimal digits precision
serial Auto-incrementing integer 4 bytes 1 to 2147483647
bigserial Auto-incrementing 8-byte integer 8 bytes 1 to 9223372036854775807

Character Data Types

Data Type Description
char(n) Fixed-length string (space-padded).
character(n) Fixed-length string (space-padded, same as char(n)).
varchar(n) Variable-length string.
character varying(n) Variable-length string (same as varchar(n)).
text Variable-length string without size limit.

Date/Time Data Types

Name Description Storage Size Minimum Value Maximum Value Resolution
timestamp[(p)][ without time zone] Date and time (no time zone). 8 bytes 4713 BC 294276 AD 1 microsecond
timestamp[(p)] with time zone Date and time with time zone. 8 bytes 4713 BC 294276 AD 1 microsecond
date Date only. 4 bytes 4713 BC 5874897 AD 1 day
time[(p)][ without time zone] Time of day only. 8 bytes 00:00:00 24:00:00 1 microsecond
time[(p)] with time zone Time of day with time zone. 12 bytes 00:00:00+14:59 24:00:00-14:59 1 microsecond
interval Time interval. 12 bytes -178000000 years 178000000 years 1 microsecond

Monetary Type

Name Description Storage Size Range
money Currency amount. 8 bytes -92233720368547758.08 to +92233720368547758.07

Binary Data Type

Name Storage Size Description
bytea 1 or 4 bytes + data Variable-length binary string.

Boolean Type

Name Description Storage Size
boolean Stores true, false, or NULL. 1 byte

Enumerated Types

(Explanation of enumerated types and example using `CREATE TYPE` would be included here.)

Geometric Data Types

Name Storage Size Representation Description
point 16 bytes (x, y) Point on a plane.
line 32 bytes ((x1, y1), (x2, y2)) Infinite line.
lseg 32 bytes ((x1, y1), (x2, y2)) Line segment.
box 32 bytes ((x1, y1), (x2, y2)) Rectangular box.
path 16 + 16n bytes ((x1, y1), ...) Path (open or closed).
polygon 40 + 16n bytes ((x1, y1), ...) Polygon.
circle 24 bytes <(x, y), r> Circle (center point and radius).

Text Search Data Types

Data Type Description
tsvector Represents a document for full-text search.
tsquery Represents a full-text search query.

UUID Data Type

(Description of UUID, its format, and alternative input forms would be included here.)

Network Address Data Types

Data Type Description Storage Size
inet IPv4 and IPv6 addresses and networks. 7 or 19 bytes
cidr IPv4 and IPv6 networks. 7 or 19 bytes
macaddr MAC addresses. 6 bytes

Key Data Type Categories

PostgreSQL's data types can be grouped into several categories:

  • Numeric: Integers (smallint, integer, bigint), decimals (decimal, numeric), and floating-point numbers (real, double precision).
  • Character: Fixed-length (char, character) and variable-length strings (varchar, character varying, text).
  • Date/Time: timestamp (with and without time zone), date, time (with and without time zone), and interval.
  • Monetary: money (stores currency amounts).
  • Binary: bytea (stores variable-length binary strings).
  • Boolean: boolean (stores true, false, or NULL).
  • Enumerated: User-defined types representing a fixed set of values.
  • Geometric: Types for representing two-dimensional shapes (point, line, lseg, box, path, polygon, circle).
  • Text Search: tsvector (for indexing documents) and tsquery (for search queries).
  • UUID: 128-bit universally unique identifiers.
  • Network Address: inet (IPv4 and IPv6), cidr (IP networks), and macaddr (MAC addresses).
  • JSON: json (text-based) and jsonb (binary) for storing JSON data.
  • Bit String: bit varying(n) and bit(n) for bit manipulation.
  • XML: xml for storing XML data.
  • Range: Types representing ranges of values (tsrange, tstzrange, daterange, int4range, int8range, numrange).
  • Arrays: Multidimensional arrays of various data types.
  • Composite: Representing a row or record as a collection of fields with specific data types.
  • Object Identifiers (OIDs): oid, regproc, regprocedure, regoper, regoperator, regclass, regtype, regnamespace, regconfig, regdictionary.
  • Pseudo-types: Special-purpose types used for function arguments and return types (any, anyelement, anyarray, anyenum, anyrange, cstring, language_handler, fdw_handler, record, trigger, pg_ddl_command, void).
  • pg_lsn: Stores Log Sequence Numbers (LSNs).

Choosing the Right Data Type

(A summary of considerations for choosing data types based on whether you have an IEEE 754 data source, integer values, large numbers, or the need to limit input would be included here.)

Conclusion

PostgreSQL's extensive type system allows you to precisely model your data. Selecting the appropriate type is crucial for both performance and data integrity.