Mastering PostgreSQL Data Types: A Comprehensive Guide

In PostgreSQL, data types shape how the database manages, retrieves, and processes information. They define the characteristics of the data that can be stored within a table's column.

Unlike MySQL, PostgreSQL offers a richer set of data types and more extensive support for custom types, making it highly adaptable to various data requirements.

PostgreSQL Data Types Categories

Category	Data Types	Description
Numeric	smallint, integer, bigint, decimal, numeric, real, double precision, smallserial, serial, bigserial	Numeric data types include various integer types for whole numbers, serial types for auto-incrementing numbers, and floating-point types for decimal numbers.
Character	char, varchar, text	Character data types are used for storing text. They vary in length from fixed (char), variable with a limit (varchar), to unlimited length (text).
Date/Time	date, time, timestamp, timestamptz, interval	These types handle dates and times, ranging from simple dates (date) to detailed timestamps (timestamp and timestamptz). interval is used for time spans.
Boolean	boolean	The Boolean data type is used for storing true or false values.
JSON	json, jsonb	JSON data types include json for storing JSON data as exact text, and jsonb for storing it in a binary format, useful for indexing.
Binary	bytea	The bytea type is used for storing binary data.
Array	Arrays of other data types (e.g., integer[], text[])	PostgreSQL supports arrays of other data types, allowing multiple values in a single column.
Enumerated	enum	Enumerated types are customizable lists of values, allowing a column to contain only specified values.
Range	int4range, int8range, numrange, tsrange, tstzrange, daterange	Range data types represent a range of values of specific types, such as integers or timestamps.
Geometric	point, line, lseg, box, path, polygon, circle	Geometric types store two-dimensional geometric data.
Network Address	cidr, inet, macaddr, macaddr8	These types are for storing network addresses like IP addresses and MAC addresses.
Bit String	bit, bit varying	Bit string types are used for storing strings of bits (0s and 1s).
Text Search	tsvector, tsquery	Text search types are used for full-text search functionality in PostgreSQL.
UUID	uuid	The uuid data type is used for storing Universally Unique Identifiers.
XML	xml	The xml data type is used for storing XML data.
Others	money, pg_lsn, txid_snapshot	These include specialized types like money for monetary values, pg_lsn for log sequence numbers, and txid_snapshot for transaction snapshots.

PostgreSQL Data Type examples

SQL Snippet:

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

CREATE TABLE user_profiles (
    user_id INT,
    user_name VARCHAR(100),
    user_preferences JSON,
    account_creation DATETIME,
    gender CHAR(1),
    current_mood mood
);

Data Type Explanations:

• INT: This is an integer data type, used for numeric values without decimal points. It is typically used for storing whole numbers like IDs.
• VARCHAR(100): This is a variable character string data type with a specified limit. VARCHAR(100) means it can store strings up to 100 characters. It's suitable for text that has variable length, such as names.
• JSON: This data type is used for storing JSON (JavaScript Object Notation) formatted data. It allows for storing complex data structures like objects and arrays in a single column.
• DATETIME: This data type is used for storing dates and times together. It records the date along with the time of day.
• CHAR(1): This is a fixed-length character type, CHAR(1) means it stores exactly one character. It’s useful for data with a known, fixed size, like gender in this example.
• ENUM: This is an enumerated type, which is a custom data type defined by a list of acceptable string values. In this case, mood is an ENUM type that can only take the values 'happy', 'sad', or 'neutral'. It's great for columns with a limited set of possible values.

PostgreSQL Data Types Cheatsheet

Here is a quick reference table that summarizes the main characteristics of the PostgreSQL data types:

Data Type	Category	Size	Range or Values	Format	Default	Example
integer	Numeric	4 bytes	-2,147,483,648 to 2,147,483,647	Whole number	0	1234
bigint	Numeric	8 bytes	-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807	Whole number	0	1234567890123
real	Numeric	4 bytes	6 decimal digits precision	Decimal	0.0	123.456
double precision	Numeric	8 bytes	15 decimal digits precision	Decimal	0.0	123.45678912345
numeric	Numeric	Variable	Up to 131072 digits before the decimal point; up to 16383 digits after the decimal point	Exact numeric	0	123.45
char(n)	Character	n bytes, fixed	Fixed-length with padding	Character string	Space-padded	'A', 'Hello'
varchar(n)	Character	Variable, up to n	Variable-length	Character string	None	'Hello, World!'
text	Character	Variable	Unlimited length	Character string	None	'Any length of text'
date	Date/Time	4 bytes	4713 BC to 5874897 AD	YYYY-MM-DD	Current date	2023-01-01
timestamp	Date/Time	8 bytes	4713 BC to 5874897 AD	YYYY-MM-DD HH:MI:SS	Current date and time	2023-01-01 12:00:00
time	Date/Time	8 bytes	00:00:00 to 24:00:00	HH:MI:SS	None	12:00:00
boolean	Boolean	1 byte	true or false	Boolean	false	true, false
json	JSON	Variable	JSON text	Textual JSON data	None	{'key': 'value'}
jsonb	JSON	Variable	Binary JSON, faster querying	Binary JSON data	None	{'key': 'value'}
bytea	Binary	Variable	Binary string	Binary data	None	E'\\xDEADBEEF'
array	Array	Variable	Collection of elements (e.g., integers, text)	Array format	None	'{1,2,3,4}'
enum	Enumerated	Variable	User-defined list of values	Enumeration of string	None	'value1', 'value2'
serial	Auto-increment	4 bytes	1 to 2,147,483,647	Whole number, auto-increment	Next ID in sequence	Automatically generated ID
bigserial	Auto-increment	8 bytes	1 to 9,223,372,036,854,775,807	Whole number, auto-increment	Next ID in sequence	Automatically generated ID
smallserial	Auto-increment	2 bytes	1 to 32,767	Whole number, auto-increment	Next ID in sequence	Automatically generated ID

Choosing between the types that look alike

Most data-type mistakes are not exotic. They come from four pairs that look interchangeable and are not.

VARCHAR vs TEXT

No performance difference. Both are stored identically, and VARCHAR without a length is exactly TEXT.

Use VARCHAR(n) when the length limit is a real business rule you want the database to enforce, such as a two-character country code. Use TEXT otherwise. The practical argument for TEXT is that changing a VARCHAR(50) to VARCHAR(100) later is an ALTER TABLE, and on a large table under load that is a migration you have to plan.

TIMESTAMP vs TIMESTAMPTZ

This is the one that causes real incidents.

TIMESTAMPTZ converts the value to UTC when it is stored and back to the session time zone when it is read, so it identifies a specific moment. TIMESTAMP stores the literal date and time with no zone attached, so the same stored value means different moments to two users in different countries.

Default to TIMESTAMPTZ. The exception is a wall-clock time that is deliberately zone-free, such as "the shop opens at 09:00" in whatever the local time happens to be.

NUMERIC vs REAL and DOUBLE PRECISION

NUMERIC is exact. REAL and DOUBLE PRECISION are binary floating point and cannot represent values like 0.1 exactly:

SELECT 0.1::float + 0.2::float = 0.3::float;   -- false
SELECT 0.1::numeric + 0.2::numeric = 0.3::numeric;  -- true

Use NUMERIC for money and anything that gets summed or compared for equality. Use the float types for measurements and scientific values where speed matters more than the last decimal place.

JSON vs JSONB

JSON keeps the exact input text, whitespace and key order included, and reparses it on every query. JSONB stores a decomposed binary form: slightly slower to write, considerably faster to read, and the only one of the two that can be GIN-indexed.

Use JSONB unless you specifically need to hand back the original document byte for byte.

Storage sizes at a glance

Type	Storage	Notes
SMALLINT	2 bytes	-32,768 to 32,767
INTEGER	4 bytes	The default choice for whole numbers
BIGINT	8 bytes	Needed past 2.1 billion, including most id columns at scale
NUMERIC(p,s)	variable	Exact, sized by digit count
REAL	4 bytes	6 decimal digits of precision
DOUBLE PRECISION	8 bytes	15 decimal digits
BOOLEAN	1 byte
DATE	4 bytes
TIMESTAMP / TIMESTAMPTZ	8 bytes each	Same size, so there is no storage reason to avoid TIMESTAMPTZ
UUID	16 bytes	Against 36 for the same value stored as TEXT
TEXT / VARCHAR	variable	String length plus 1 to 4 bytes

Column order affects real table size too, because PostgreSQL aligns fixed-width columns. Grouping the 8-byte types together and putting variable-length columns last can measurably reduce padding on wide tables.

PostgreSQL Data Types Tips and Tricks

1. Use the Right Numeric Type for Efficiency: Choose SMALLINT, INTEGER, or BIGINT based on the range of values you expect. Using a smaller type than necessary can save significant disk space, especially in large tables.

2. Treat VARCHAR and TEXT as interchangeable: In PostgreSQL, there is no performance difference between VARCHAR and TEXT. Use VARCHAR when you need to enforce a maximum length, and TEXT for longer or unrestricted text fields.

3. Prefer TIMESTAMP WITH TIME ZONE: When working with timestamps, TIMESTAMP WITH TIME ZONE (abbreviated as TIMESTAMPTZ) should be your default choice. It automatically adjusts for time zone differences, which is crucial for applications serving users in multiple time zones.

4. Optimize JSON Storage with JSONB: While JSON and JSONB both store JSON data, JSONB stores data in a decomposed binary format, making it slightly slower to input but significantly faster to query. It also allows for indexing, which is a huge advantage for performance.

5. Beware of Implicit Type Casting: PostgreSQL automatically converts types where it makes sense (like from INTEGER to FLOAT), but relying on implicit casting can sometimes lead to unexpected results. Be explicit with your casts to avoid bugs.

6. Querying JSON/JSONB Data: Use the arrow operators (->, ->>) to query JSON or JSONB objects. These operators can extract an element from a JSON object.

   SELECT user_preferences -> 'theme' AS theme
   FROM user_profiles
   WHERE user_preferences ->> 'notifications' = 'enabled';
   

   This snippet selects the 'theme' from the 'user_preferences' JSON column where 'notifications' are enabled.

7. Casting Data Types: Convert (cast) data types using the :: operator. This is useful when you need to transform a value into another type, like converting a string to a date.

   SELECT '2023-01-01'::DATE;
   

   This example casts a string to a date.

8. Using Arrays: Arrays can store multiple values in a single column. You can query array elements using array positions.

   SELECT responses[1] AS first_response
   FROM surveys;
   

   This snippet retrieves the first element from the 'responses' array column.

9. Full-Text Search with tsvector: For full-text search, use the tsvector data type. It allows indexing of text for efficient searching.

   SELECT description
   FROM articles
   WHERE to_tsvector(description) @@ to_tsquery('PostgreSQL');
   

   This query searches for articles that contain the word 'PostgreSQL' in their description.

10. Extracting Data from Dates and Times: Use date/time functions like EXTRACT to get specific parts of a date or time.

    SELECT EXTRACT(YEAR FROM account_creation) AS year_created
    FROM user_profiles;
    

    This snippet extracts the year from the 'account_creation' date-time column.
    

Conclusion

In this article, we've covered the PostgreSQL data types, including their categories, examples, storage sizes, and how to choose between the pairs that look interchangeable. We hope this guide helps you understand the PostgreSQL data types and how to use them effectively ✌️

FAQ

One practical note: custom and extension-provided types have to exist on the target server before a dump that uses them will restore. The complete guide to PostgreSQL backup covers what a logical dump does and does not carry with it.