PostgreSQL - Data Types

PostgreSQL - Modern Data Types

In this chapter, we will explore the rich set of data types available in PostgreSQL. When creating a table, you must specify a data type for each column. This defines the kind of data the column can hold, such as integers, text, or dates.

Properly defining data types provides several key advantages:

• Data Integrity: Ensures that only valid data is stored. For example, a DATE column will reject an invalid date format.
• Performance: Using the most appropriate data type allows PostgreSQL to store data efficiently and process queries faster.
• Consistency: Operations on columns of the same type produce predictable and consistent results.
• Functionality: Unlocks a wide range of type-specific functions and operators (e.g., date arithmetic, JSON manipulation).

PostgreSQL offers a comprehensive set of standard SQL data types. Furthermore, it allows users to define their own custom data types using the CREATE TYPE command, providing great extensibility.

Numeric Types

Numeric types are used for storing quantitative data. They include integers of various sizes, floating-point numbers, and exact-precision decimals.

Name	Storage Size	Description	Range
smallint	2 bytes	Small-range integer	-32768 to +32767
integer	4 bytes	The standard choice for integers	-2147483648 to +2147483647
bigint	8 bytes	Large-range integer for very large numbers	-9223372036854775808 to 9223372036854775807
numeric(p, s), decimal(p, s)	variable	User-specified precision, exact. Best for financial data.	Up to 131072 digits before and 16383 digits after the decimal point.
real	4 bytes	Variable-precision, inexact, single precision	6 decimal digits precision
double precision	8 bytes	Variable-precision, inexact, double precision	15 decimal digits precision

Auto-incrementing Types (Identity Columns)

For creating auto-incrementing primary keys, the modern, SQL-standard approach is to use identity columns. The older serial types are still supported but are now considered legacy.

CREATE TABLE products (
    product_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_name TEXT NOT NULL
);

-- The older 'serial' syntax is equivalent to:
CREATE TABLE products_legacy (
    product_id SERIAL PRIMARY KEY, -- This is older syntax
    product_name TEXT NOT NULL
);

GENERATED ALWAYS AS IDENTITY is preferred over SERIAL because it’s more standards-compliant and prevents users from accidentally inserting or updating the identity column.

Monetary Types

While PostgreSQL offers a money type, it is generally not recommended for modern applications. Its behavior is locale-dependent, and it can suffer from rounding errors during calculations. For financial and currency data, always use the NUMERIC or DECIMAL type, which provide exact precision.

-- Best Practice: Use NUMERIC for currency
CREATE TABLE transactions (
    transaction_id INT GENERATED ALWAYS AS IDENTITY,
    amount NUMERIC(10, 2) NOT NULL -- e.g., for amounts up to 99,999,999.99
);

Character Types

These types store text strings.

Name	Description
character varying(n), varchar(n)	Variable-length string with a maximum length limit.
character(n), char(n)	Fixed-length string, blank-padded to the specified length.
text	Variable-length string with no predefined limit.

Best Practice: In modern PostgreSQL, there is no performance difference between varchar(n) and text. It’s often recommended to use text by default, unless you need a specific length constraint for data validation (e.g., a 2-character country code), in which case you can use varchar(2) or add a CHECK constraint.

Binary Data Types

The bytea data type is used for storing binary data, such as images, audio files, or other non-textual information.

Name	Description
bytea	Variable-length binary string.

Date/Time Types

PostgreSQL has a robust set of types for handling dates and times. All types have microsecond precision, except date.

Name	Storage Size	Description
timestamp [without time zone]	8 bytes	Both date and time. Use with caution as it’s timezone-unaware.
timestamptz [with time zone]	8 bytes	Both date and time, with timezone awareness. Highly Recommended.
date	4 bytes	Date only (no time of day).
time [without time zone]	8 bytes	Time of day only (no date).
timetz [with time zone]	12 bytes	Time of day only, with timezone awareness.
interval	16 bytes	Represents a duration of time (e.g., ‘2 days’ or ‘3 hours’).

Best Practice: Almost always use timestamptz instead of timestamp. timestamptz stores timestamps in UTC internally and converts them to the client’s session timezone on retrieval, preventing ambiguity and bugs related to timezones.

Boolean Type

The standard SQL boolean type can hold one of three states: true, false, or NULL (representing an ‘unknown’ state). Valid literal values include TRUE, FALSE, 't', 'f', '1', and '0'.

Name	Storage Size	Description
boolean	1 byte	Logical state of true, false, or null.

Enumerated Types (ENUM)

Enumerated types are custom types that comprise a static, ordered set of string labels. They are useful for representing a value that must be one of a predefined list of values, like status codes or categories.

-- First, create the type
CREATE TYPE order_status AS ENUM ('pending', 'processing', 'shipped', 'delivered', 'cancelled');

-- Then, use it in a table definition
CREATE TABLE orders (
    order_id INT GENERATED ALWAYS AS IDENTITY,
    status order_status NOT NULL DEFAULT 'pending'
);

Enums are more efficient and type-safe than using a simple text column with a CHECK constraint for this purpose.

Geometric Types

Geometric types represent two-dimensional spatial objects. While useful for basic geometric calculations, for advanced geospatial analysis, the PostGIS extension is the industry standard and should be used instead.

Name	Representation	Description
point	(x,y)	A point on a plane
lseg	((x1,y1),(x2,y2))	A finite line segment
box	((x1,y1),(x2,y2))	A rectangular box
path	((x1,y1),…)	A closed path (similar to a polygon)
polygon	((x1,y1),…)	A polygon
circle	<(x,y),r>	A center point and radius

Network Address Types

PostgreSQL provides specialized types for storing network addresses, offering validation and type-specific operators.

Name	Storage Size	Description
inet	7 or 19 bytes	Stores an IPv4 or IPv6 host address, optionally with its subnet.
cidr	7 or 19 bytes	Stores an IPv4 or IPv6 network address in CIDR notation.
macaddr	6 bytes	Stores a 6-byte MAC address (EUI-48).
macaddr8	8 bytes	Stores an 8-byte MAC address (EUI-64). Added in PostgreSQL 10.

JSON Types

PostgreSQL offers powerful support for storing and querying JSON data. There are two types: json and jsonb.

• json: Stores an exact copy of the input text. It’s faster to insert but slower to process because it must be re-parsed for every operation.
• jsonb: Stores JSON in a decomposed binary format. It’s slightly slower to insert due to conversion overhead, but significantly faster to query. It also supports indexing. jsonb is the recommended type for almost all use cases.

CREATE TABLE user_profiles (
    user_id INT PRIMARY KEY,
    profile_data JSONB
);

INSERT INTO user_profiles (user_id, profile_data)
VALUES (1, '{"name": "Alice", "tags": ["dev", "sql"], "active": true}');

-- Querying JSONB data with specific operators
SELECT profile_data -> 'name' AS user_name
FROM user_profiles
WHERE profile_data ->> 'active' = 'true';

Array Types

Any data type in PostgreSQL can be used to create a variable-length multidimensional array. Arrays are useful for storing lists of values within a single column.

CREATE TABLE posts (
    post_id INT GENERATED ALWAYS AS IDENTITY,
    title TEXT,
    tags TEXT[], -- A one-dimensional array of text
    visitor_scores INT[] -- A one-dimensional array of integers
);

-- Inserting data using the modern ARRAY constructor
INSERT INTO posts (title, tags)
VALUES ('PostgreSQL is Awesome', ARRAY['sql', 'database', 'postgres']);

-- Searching for a specific element in an array
SELECT title, tags FROM posts WHERE 'sql' = ANY(tags);

UUID Type

A UUID (Universally Unique Identifier) is a 128-bit value used to uniquely identify information in computer systems. It’s an excellent choice for primary keys in distributed systems. An example is a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11.

To generate UUIDs, you can use the gen_random_uuid() function (available in PostgreSQL 13+) or enable the pgcrypto extension for the gen_random_uuid() function in older versions.

-- For PostgreSQL 13+
CREATE TABLE documents (
    doc_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    content TEXT
);

Other Specialized Types

PostgreSQL also includes many other specialized types:

• XML Type: For storing XML data, with functions for validation and XPath queries.
• Range Types: Represent a range of values, such as int4range (range of integers) or daterange (range of dates). Useful for scheduling and time-based logic.
• Text Search Types: tsvector and tsquery are used to implement powerful full-text search capabilities.
• Composite Types: A type that represents the structure of a row, bundling multiple fields into a single column. Often used as return types for functions.

Deprecated Types

Object Identifier Types (OIDs): In older versions of PostgreSQL, OIDs were used as internal primary keys for tables. The feature to add an oid column to user tables (WITH OIDS) is now deprecated and has been removed since PostgreSQL 12. You should always define an explicit primary key for your tables using types like INTEGER, BIGINT, or UUID.