Mastering PostgreSQL Data Types: A Comprehensive Guide

SimpleBackups founder

Laurent Lemaire

Co-founder, SimpleBackups

November 30, 2023

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

Table of Contents

PostgreSQL Data Types Categories

CategoryData TypesDescription
Numericsmallint, integer, bigint, decimal, numeric, real, double precision, smallserial, serial, bigserialNumeric data types include various integer types for whole numbers, serial types for auto-incrementing numbers, and floating-point types for decimal numbers.
Characterchar, varchar, textCharacter data types are used for storing text. They vary in length from fixed (char), variable with a limit (varchar), to unlimited length (text).
Date/Timedate, time, timestamp, timestamptz, intervalThese types handle dates and times, ranging from simple dates (date) to detailed timestamps (timestamp and timestamptz). interval is used for time spans.
BooleanbooleanThe Boolean data type is used for storing true or false values.
JSONjson, jsonbJSON data types include json for storing JSON data as exact text, and jsonb for storing it in a binary format, useful for indexing.
BinarybyteaThe bytea type is used for storing binary data.
ArrayArrays of other data types (e.g., integer[], text[])PostgreSQL supports arrays of other data types, allowing multiple values in a single column.
EnumeratedenumEnumerated types are customizable lists of values, allowing a column to contain only specified values.
Rangeint4range, int8range, numrange, tsrange, tstzrange, daterangeRange data types represent a range of values of specific types, such as integers or timestamps.
Geometricpoint, line, lseg, box, path, polygon, circleGeometric types store two-dimensional geometric data.
Network Addresscidr, inet, macaddr, macaddr8These types are for storing network addresses like IP addresses and MAC addresses.
Bit Stringbit, bit varyingBit string types are used for storing strings of bits (0s and 1s).
Text Searchtsvector, tsqueryText search types are used for full-text search functionality in PostgreSQL.
UUIDuuidThe uuid data type is used for storing Universally Unique Identifiers.
XMLxmlThe xml data type is used for storing XML data.
Othersmoney, pg_lsn, txid_snapshotThese 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 TypeCategorySizeRange or ValuesFormatDefaultExample
integerNumeric4 bytes-2,147,483,648 to 2,147,483,647Whole number01234
bigintNumeric8 bytes-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807Whole number01234567890123
realNumeric4 bytes6 decimal digits precisionDecimal0.0123.456
double precisionNumeric8 bytes15 decimal digits precisionDecimal0.0123.45678912345
numericNumericVariableUp to 131072 digits before the decimal point; up to 16383 digits after the decimal pointExact numeric0123.45
char(n)Charactern bytes, fixedFixed-length with paddingCharacter stringSpace-padded'A', 'Hello'
varchar(n)CharacterVariable, up to nVariable-lengthCharacter stringNone'Hello, World!'
textCharacterVariableUnlimited lengthCharacter stringNone'Any length of text'
dateDate/Time4 bytes4713 BC to 5874897 ADYYYY-MM-DDCurrent date2023-01-01
timestampDate/Time8 bytes4713 BC to 5874897 ADYYYY-MM-DD HH:MI:SSCurrent date and time2023-01-01 12:00:00
timeDate/Time8 bytes00:00:00 to 24:00:00HH:MI:SSNone12:00:00
booleanBoolean1 bytetrue or falseBooleanfalsetrue, false
jsonJSONVariableJSON textTextual JSON dataNone{'key': 'value'}
jsonbJSONVariableBinary JSON, faster queryingBinary JSON dataNone{'key': 'value'}
byteaBinaryVariableBinary stringBinary dataNoneE'\\xDEADBEEF'
arrayArrayVariableCollection of elements (e.g., integers, text)Array formatNone'{1,2,3,4}'
enumEnumeratedVariableUser-defined list of valuesEnumeration of stringNone'value1', 'value2'
serialAuto-increment4 bytes1 to 2,147,483,647Whole number, auto-incrementNext ID in sequenceAutomatically generated ID
bigserialAuto-increment8 bytes1 to 9,223,372,036,854,775,807Whole number, auto-incrementNext ID in sequenceAutomatically generated ID
smallserialAuto-increment2 bytes1 to 32,767Whole number, auto-incrementNext ID in sequenceAutomatically generated ID

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. Leverage VARCHAR and TEXT Interchangeably:
    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.

    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.


In this article, we've covered the PostgreSQL data types, including their categories, examples, and tips and tricks. We hope this guide helps you understand the PostgreSQL data types and how to use them effectively ✌️

Back to blog

Stop worrying about your backups.
Focus on building amazing things!

Free 7-day trial. No credit card required.

Have a question? Need help getting started?
Get in touch via chat or at [email protected]

Customer support with experts
Security & compliance
Service that you'll love using