A Cheat Sheet of PostgreSQL Data Types for Web Developers
Pick the right data types for your applications
PostgreSQL is recently my number one choice thanks to its powerful features, reliability, and flexibility.
In this article, we will look into PostgreSQL data types, exploring the different options of data types and when to use them.
Text Data Types
Text data types in PostgreSQL are essential for storing and manipulating textual data. There are three primary text data types:
CHAR(N)
Fixed-length character string with a user-defined length N. If the inserted string is shorter than N, it will be padded with spaces.
VARCHAR(N)
Variable-length character string with a user-defined maximum length N. Unlike CHAR, VARCHAR does not pad the inserted string with spaces.
TEXT
Variable-length character string with unlimited length. This type is best suited for storing large amounts of textual data, such as articles or user-generated content.
Numeric Data Types
PostgreSQL offers various numeric data types for different use cases:
INTEGER
A 32-bit signed integer with a range of -2,147,483,648 to 2,147,483,647.
SMALLINT
A 16-bit signed integer ranging from -32,768 to 32,767. Use this type when storage is a concern and the range is sufficient.
BIGINT
A 64-bit signed integer with a range of -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 (so yes pretty "big" numbers). This type is suitable for large numbers that exceed the INTEGER range.
SERIAL, SMALLSERIAL and BIGSERIAL
Auto-incrementing integer types, ideal for primary keys or unique identifiers. SERIAL corresponds to INTEGER, SMALLSERIAL to SMALLINT, and BIGSERIAL to BIGINT.
NUMERIC(P, S) or DECIMAL(P, S):
Exact numeric types with user-defined precision (P) and scale (S). Precision defines the total number of digits, while scale defines the number of digits after the decimal point. These types are suitable for monetary or scientific calculations that require exact values.
REAL
A single-precision floating-point number, which can store approximations of real numbers with 6 decimal digits of precision.
DOUBLE PRECISION
A double-precision floating-point number, providing more accurate approximations of real numbers with 15 decimal digits of precision. Date and Time Data Types Handling date and time is a common requirement in web applications. PostgreSQL provides various data types for this purpose:
DATE
When storing a date value, PostgreSQL uses the yyyy-mm-dd format e.g. 1993-08-21. This format is also used in for inserting data in PostgreSQL.
TIME
Stores time of day without any date information. There are two variants: TIME without timezone and TIME WITH TIMEZONE.
TIMESTAMP
Combines date and time information. Like TIME, there are two variants: TIMESTAMP without timezone and TIMESTAMP WITH TIMEZONE (TIMESTAMPZ can be used as shorthand).
INTERVAL
Represents a time span or duration, such as "2 hours" or "3 days".
Binary Data Types
Binary data types in PostgreSQL help store data that doesn't fit into traditional textual or numeric types, such as images or encrypted data. PostgreSQL offers two primary binary data types:
BYTEA
Variable-length binary data with a maximum length of 4 terabytes. This type is suitable for storing binary files like images, audio files, or serialized objects.
BLOB
An alias for BYTEA, providing the same functionality.
Boolean Data Types
Boolean data types are essential for storing true or false values in your database:
BOOLEAN
Represents true, false, or NULL values. In PostgreSQL, you can use 't' or 'f', 'true' or 'false', '1' or '0', and 'yes' or 'no' as literals for true and false values.
Enumerated Types
Enumerated types (ENUM) are user-defined data types that consist of a static, ordered set of values. They are helpful in representing a fixed set of values, such as days of the week, order statuses, or user roles:
ENUM
A custom data type that stores one of the predefined set of values. For example, you can create an ENUM type for user roles like this: CREATE TYPE user_role AS ENUM ('admin', 'editor', 'user').
UUID Data Types
Universally Unique Identifiers (UUID) are 128-bit values that can be used as unique identifiers in your database:
UUID
A UUID data type for storing unique identifiers. PostgreSQL supports several UUID generation functions, such as uuid_generate_v4() for generating random UUIDs.
JSON Data Types
JSON data types are becoming increasingly popular for storing and manipulating semi-structured data. PostgreSQL provides two JSON data types:
JSON
Stores JSON data as plain text. This type supports standard JSON operations but does not enforce strict JSON validation.
JSONB
Stores JSON data in a binary format, allowing for faster query performance and more efficient storage.
Note: JSONB enforces strict JSON validation and provides advanced indexing options, making it the preferred choice for most use cases.
Array Data Types
Array data types allow you to store multiple values of the same data type in a single column. PostgreSQL supports one-dimensional and multi-dimensional arrays for all built-in data types:
data_type[]
Represents a one-dimensional array of the specified data type. For example, INTEGER[] would represent an array of integers.
data_type[][]
Represents a multi-dimensional array of the specified data type. For example, TEXT[][] would represent a two-dimensional array of text strings.
Range Data Types
Range data types help represent continuous ranges of values, such as date ranges or numeric intervals:
INT4RANGE
Range of INTEGER.
INT8RANGE
Range of BIGINT.
NUMRANGE
Range of NUMERIC.
TSRANGE
Range of TIMESTAMP WITHOUT TIME ZONE.
TSTZRANGE
Range of TIMESTAMP WITH TIME ZONE.
DATERANGE
Range of DATE.
Checkout the PostgreSQL documentation so you can see it in more action. It's one of the types I have the least experience with.
