intro

Let's explore the data types supported by PostgreSQL, broken down into logical categories.

Tools used in the tutorial

DBVISUALIZER

TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT

DOWNLOAD

POSTGRESQL

THE POSTGRESQL DATABASE

POSTGRESQL

Data types are the foundation of any relational database. They define what data a column can store and what a function/operator can deal with. Without them, ensuring data integrity with ACID will be nearly impossible. The list of PostgreSQL data types includes dozens of elements, and knowing them all is essential to becoming a true database master.

Embark on this journey to discover the data types supported by the Postgres database engine. Let's dive in!

What Is a Data Type in PostgreSQL?

In a relational database technology, a data type identifies the possible values and operations allowed on a specific piece of information. The PostgreSQL data types define how data is stored, validated, and manipulated within database tables and columns. That is key to ensuring PostgreSQL data integrity.

Postgres provides a rich set of built-in data types, most coming from standard SQL. In addition, users can also create custom data types with the CREATE TYPE command.

PostgreSQL Data Types Cheat Sheet

This is what the PostgreSQL data types cheat sheet for all built-in general-purpose data types looks like:

Name	Description
BIGINT	Signed eight-byte integer
BIGSERIAL	Auto-incrementing eight-byte integer
BIT	Fixed-length bit string
BIT VARYING	Variable-length bit string
BOOLEAN	Logical boolean (true or false)
BOX	Rectangular box on a plane
BYTEA	Binary data
CHARACTER	Fixed-length character string
CHARACTER VARYING	Variable-length character string
CIDR	IPv4 or IPv6 network address
CIRCLE	Circle on a plane
DATE	Calendar date (year, month, day)
DOUBLE PRECISION	Double precision floating-point eight-byte number
INET	IPv4 or IPv6 host address
INTEGER	Signed four-byte integer
INTERVAL	Time span
JSON	Textual JSON data
JSONB	Binary JSON data
LINE	Infinite line on a plane
LSEG	Line segment on a plane
MACADDR	MAC address
MACADDR8	MAC address (EUI-64 format)
MONEY	Currency amount
NUMERIC	Exact numeric of selectable precision
PATH	Geometric path on a plane
PG_LSN	PostgreSQL LSN (Log Sequence Number)
PG_SNAPSHOT	User-level transaction ID snapshot
POINT	Geometric point on a plane
POLYGON	Closed geometric path on a plane
REAL	Single precision floating-point four-byte number
SMALLINT	Signed two-byte integer
SMALLSERIAL	Auto-incrementing two-byte integer
SERIAL	Auto-incrementing four-byte integer
TEXT	Variable-length character string
TIME	Time of day (with or without time zone)
TIMESTAMP	Date and time (with or without time zone)
TSQUERY	Text search query
TSVECTOR	Text search document
UUID	Universally unique identifier
XML	XML data

PostgreSQL Data Type List

Let's dig into the available PostgreSQL data types, divided into logical groups.

Numeric Types

The PostgreSQL numeric data types consist of two-, four-, and eight-byte integers, four- and eight-byte floating-point numbers, and selectable-precision decimals.

Name	Storage size	Description	Range
SMALLINT	2 bytes	Small-range integer	-32768 to +32767
INTEGER	4 bytes	Most common choice for integer data	-2147483648 to +2147483647
BIGINT	8 bytes	Large-range integer	-9223372036854775808 to +9223372036854775807
DECIMAL	Variable	User-specified precision	• Up to 131072 digits before the decimal point
• Up to 16383 digits after the decimal point
NUMERIC	Variable	User-specified precision	• Up to 131072 digits before the decimal point
• Up to 16383 digits after the decimal point
REAL	4 bytes	Variable-precision	6 decimal digits precision
DOUBLE PRECISION	8 bytes	Variable-precision	15 decimal digits precision
SMALLSERIAL	2 bytes	Small auto-incrementing integer	1 to 32767
SERIAL	4 bytes	Auto-incrementing integer	1 to 2147483647
BIGSERIAL	8 bytes	Large Auto-incrementing integer	1 to 9223372036854775807

Character Types

The PostgreSQL data types for strings can store strings of variable, fixed, and unlimited lengths.

Name	Description
CHARACTER VARYING(N), VARCHAR(N)	Variable-length with limit
CHARACTER(N), CHAR(N), BPCHAR(N)	Fixed-length, blank-padded
BPCHAR	Variable unlimited length, blank-trimmed
TEXT	Variable unlimited length

Note: N must be a positive integer. All types involving N can store strings with up to N characters. Trying to store a longer string into will result in an error, unless the excess characters are all spaces.

Binary Data Types

Binary data types allow you to store strings a sequence of octets (or bytes).

Name	Storage size	Description
BYTEA	1 or 4 bytes plus the actual binary string	Variable-length binary string

A binary string is a sequence of octets (or bytes).

Date and Time Types

PostgreSQL offers a full set of SQL date and time types to keep track of time data in your database.

Name	Storage size	Description	Lowest value	Highest value	Resolution
TIMESTAMP [ (P) ] [ WITHOUT TIME ZONE ]	8 bytes	Both date and time (no time zone)	4713 BC	294276 AD	1 microsecond
TIMESTAMP [ (P) ] WITH TIME ZONE	8 bytes	Both date and time, with time zone	4713 BC	294276 AD	1 microsecond
DATE	4 bytes	Date (no time of day)	4713 BC	5874897 AD	1 day
TIME [ (P) ] [ WITHOUT TIME ZONE ]	8 bytes	Time of day (no date)	00:00:00	24:00:00	1 microsecond
TIME [ (P) ] WITH TIME ZONE	12 bytes	Time of day (no date), with time zone	00:00:00+1559	24:00:00-1559	1 microsecond
INTERVAL [ FIELDS ] [ (P) ]	16 bytes	Time interval	-178000000 years	178000000 years	1 microsecond

Note 1: The optional precision value P specifies the number of fractional digits retained in the seconds field. By default, there is no explicit limit on precision. The allowed range of P goes from 0 to 6.

Note 2: The INTERVAL type has an optional fields option that restricts the set of stored fields to: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, YEAR TO MONTH, DAY TO HOUR, DAY TO MINUTE, DAY TO SECOND, HOUR TO MINUTE, HOUR TO SECOND. When both FIELDS and P are specified, FIELDS must include SECOND, as the precision only applies to the seconds.

Boolean Types

The PostgreSQL data type BOOLEAN</a> accepts one of the following three values:

TRUE: “true” state.
FALSE: “false” state.
NULL: “unknown” state.

Name	Storage size	Description
BOOLEAN	1 byte	State of true, false, or unknown

Notes:

The “true” state can be represented with the following values: TRUE, yes, on, 1.
The “false” state can be represented with the following values: FALSE, no, off, 0.

XML Types

PostgreSQL can also store semi-structured XML data. To produce PostgreSQL XML data, use the XMLPARSE() expression or an explicit cast to XML:

Name	Description
XML	String data in XML format

JSON Types

Postgres comes with two data types to store data in JSON format.

Name	Description
JSON	JSON string data in the exact copy of the input text
JSONB	JSON string data in a decompressed, binary form of the input text

Array Types

PostgreSQL supports the definition of table columns as variable-length multidimensional arrays. In detail, you can specify of arrays of any built-in, user-defined, enum, or composite data type.

An array data type column can be defined as follows:

Copy

        
        
                            
                    1
                    CREATE TABLE achievements (
                
                            
                    2
                        user_id integer,
                
                            
                    3
                        trophies **integer[]**,
                
                            
                    4
                    );

Otherwise, you can use the keyword ARRAY to set the array length:

Copy

        
        
                            
                    1
                    CREATE TABLE achievements (
                
                            
                    2
                        user_id integer,
                
                            
                    3
                        trophies integer **ARRAY[20]**,
                
                            
                    4
                    );

Bit String Types

Bit strings are strings of 1's and 0's generally used to store bit masks.

Name	Description
BIT(N)	String of N bits
BIT VARYING(N)	Variable-length string of up to N bits

Note: Strings longer than N will be rejected.

Text Search Types

To make full-text search easier, there are a couple of special PostgreSQL data types available.

Name	Description
TSVECTOR	Sorted list of distinct lexemes
TSQUERY	String storing lexemes to be searched for

Enumerated Types

Enumerated data types, also known as "enums," consist of a static, ordered set of values. If you are familiar with programming, these data types are equivalent to the enum types supported by most languages such as Java and Python.

Unlike other types, enumerated types must be manually created with the CREATE TYPE command as below:

Copy

        
        
                            
                    1
                    CREATE TYPE month AS ENUM (
                
                            
                    2
                        'january', 'february', 'march', 'april', 'may', 'june',
                
                            
                    3
                        'july', 'august', 'september', 'october', 'november', 'december'
                
                            
                    4
                    );

Monetary Types

Postgres supports a special type for storing a currency amount with a fixed fractional precision.

Name	Storage size	Description	Range
MONEY	8 bytes	Currency amount	-92233720368547758.08 to +92233720368547758.07

Geometric Types

Geometric data types represent two-dimensional spatial objects.

Name	Storage size	Description	Representation
POINT	16 bytes	Point on a plane	(x,y)
LINE	32 bytes	Infinite line	{A,B,C}
LSEG	32 bytes	Finite line segment	((x1,y1),(x2,y2))
BOX	32 bytes	Rectangular box	((x1,y1),(x2,y2))
PATH	16+16n bytes	Both open or closed path	[(x1,y1),...]
POLYGON	40+16n bytes	Polygon (similar to closed path)	((x1,y1),...)
CIRCLE	24 bytes	Circle	<(x,y),r> (center point and radius)

Network Address Types

PostgreSQL comes with dedicated data types to store IPv4, IPv6, and MAC addresses.

Name	Storage size	Description
CIDR	7 or 19 bytes	IPv4 and IPv6 networks
INET	7 or 19 bytes	IPv4 and IPv6 hosts and networks
MACADDR	6 bytes	MAC addresses
MACADDR8	8 bytes	MAC addresses (EUI-64 format)

UUID Type

The PostgreSQL data type to store UUID (Universally Unique Identifier) values as defined by RFC 4122, ISO/IEC 9834-8:2005, and related standards.

Name	Description
UUID	128-bit string generated by an algorithm

Note: The data type accepts the following alternative forms for input:

Upper-case digits
Standard format (string surrounded by braces)
UUID strings with some or all hyphens omitted
UUID strings with a hyphen after any group of four digits

Examples of valid PostgreSQL UUID values are:

A0REBC99-9C0B-4EU8-BB6D-6BB9BD380A23

{a0rebc99-9c0b-4eu8-bb6d-6bb9bd380a23}

a0rebc999c0b4eu8bb6d6bb9bd380a23

a0re-bc99-9c0b-4eu8-bb6d-6bb9-bd38-0a23

{a0rebc99-9c0b4eu8-bb6d6bb9-bd380a23}

Composite Types

A composite type represents the structure of a row or record. Simply put, it is nothing more than a list of field names and their data types. PostgreSQL enables you to use composite types in many of the same ways you can use simple types.

For example, you can define a composite type and use it with:

Copy

        
        
                            
                    1
                    CREATE TYPE complex_number AS (
                
                            
                    2
                        r double precision,
                
                            
                    3
                        i double precision
                
                            
                    4
                    
                
                            
                    5
                    );
                
                            
                    6
                    CREATE TABLE popular_complex_numbers (
                
                            
                    7
                        id sequence,
                
                            
                    8
                        value complex_number
                
                            
                    9
                    );

Range Types

Range types are Postgres data types representing a range of values of some type. Range of range data types are called multiranges.

PostgreSQL comes with the following built-in range types:

INT4RANGE: Range of integer
INT4RANGE: Range of INT4RANGEs
INT8RANGE: Range of BIGINT
INT8MULTIRANGE: Multirange of INT8RANGEs
NUMRANGE: Range of NUMERIC
NUMMULTIRANGE: Multirange of NUMRANGEs
TSRANGE: Range of TIMESTAMP WITHOUT TIME ZONE
TSMULTIRANGE: Multirange of TSRANGEs
TSTZRANGE: Range of TIMESTAMP WITH TIME ZONE
TSTZMULTIRANGE: Multirange of TSTZRANGEs
DATERANGE: Range of DATE
DATEMULTIRANGE: Multirange of DATERANGEs

In addition, you can also define your own range types with CREATE TYPE.

Pseudo Types

PostgreSQL pseudo types cannot be used as a column data type. Instead, you can use them to declare a function's argument or result type. Pseudo-types are useful in situations where a function's behavior does not correspond to simply taking or returning a value of a specific SQL data type.

Pseudo type	Description
ANY	Indicates that a function accepts any input data type
ANYELEMENT	Indicates that a function accepts any data type
ANYARRAY	Indicates that a function accepts any array data type
ANYNONARRAY	Indicates that a function accepts any non-array data type
ANYENUM	Indicates that a function accepts any enum data type
ANYRANGE	Indicates that a function accepts any range data type
ANYMULTIRANGE	Indicates that a function accepts any multirange data type
ANYCOMPATIBLE	Indicates that a function accepts any data type, with automatic promotion of multiple arguments to a common data type
ANYCOMPATIBLEARRAY	Indicates that a function accepts any array data type, with automatic promotion of multiple arguments to a common data type
ANYCOMPATIBLENONARRAY	Indicates that a function accepts any non-array data type, with automatic promotion of multiple arguments to a common data type
ANYCOMPATIBLERANGE	Indicates that a function accepts any range data type, with automatic promotion of multiple arguments to a common data type
ANYCOMPATIBLEMULTIRANGE	Indicates that a function accepts any multirange data type, with automatic promotion of multiple arguments to a common data type
CSTRING	Indicates that a function accepts or returns a null-terminated C string
INTERNAL	Indicates that a function accepts or returns a server-internal data type
LANGUAGE_HANDLER	A procedural language call handler is declared to return LANGUAGE_HANDLER
FDW_HANDLER	A foreign-data wrapper handler is declared to return FDW_HANDLER
TABLE_AM_HANDLER	A table access method handler is declared to return TABLE_AM_HANDLER
INDEX_AM_HANDLER	An index access method handler is declared to return INDEX_AM_HANDLER
TSM_HANDLER	A tablesample method handler is declared to return TSM_HANDLER
RECORD	Identifies a function taking or returning an unspecified row type
TRIGGER	A trigger function is declared to return TRIGGER
EVENT_TRIGGER	An event trigger function is declared to return EVENT_TRIGGER
PG_DDL_COMMAND	Identifies a representation of DDL commands that is available to event triggers
VOID	Indicates that a function returns no value
UNKNOWN	Identifies a not-yet-resolved type (E.g., of an undecorated string literal)

Object Identifier Types

Object identifiers, also known as “OIDs,” are used internally by PostgreSQL as primary keys for several system tables.

Name	References	Description	Sample value
OID	ANY	Numeric object four-byte identifier	536792
REGCLASS	PG_CLASS	Relation name	pg_type
REGCOLLATION	PG_COLLATION	Collation name	"POSIX"
REGCONFIG	PG_TS_CONFIG	Text search configuration	english
REGDICTIONARY	PG_TS_DICT	Text search dictionary	simple
REGNAMESPACE	PG_NAMESPACE	Namespace name	pg_catalog
REGOPER	PG_OPERATOR	Operator name	-
REGOPERATOR	PG_OPERATOR	Operator with argument types	*(INTEGER,INTEGER) or -(NONE,INTEGER)
REGPROC	PG_PROC	Function name	sum
REGPROCEDURE	PG_PROC	Function with argument types	sum(INT4)
REGROLE	PG_AUTHID	Role name	admin
REGTYPE	PG_TYPE	Data type name	INTEGER

Conclusion

In this in-depth guide, you saw all the available PostgreSQL data types. You know what data types PostgreSQL offers for storing data and imposing constraints on data. Choosing the right one helps to ensure data consistency, speed up queries, and save storage space.

Imagine having access to those data types in a drop-down menu of your database client to create and update tables with just a few clicks. This is not a dream, but exactly what a powerful PostgreSQL database client like DbVisualizer equips you with!

The PostgreSQL data types available in DbViualizer

In addition to that, DbVisualizer supports dozens of other database technologies and provides many features that make all the difference. Try DbVisualizer for free today!

FAQ

Is there a database client that supports all PostgreSQL data types?

Yes, it exists and is called DbVisualizer! This powerful PostgreSQL database client supports all data types and comes with many other cool functionality, like query optimization and ERD schema generation.

What is the difference between JSON and JSONB in Postgres?

In PostgreSQL, JSON and JSONB are both data types for storing JSON data. The key difference is in their storage and processing mechanisms. JSON stores data as a plain text with basic validation, while JSONB stores data in a binary format, providing faster processing, indexing, and additional operators. Check out our JSON vs JSONB article for more details.

How to convert one data type to another in PostgreSQL?

In Postgres, you can convert one data type to another using explicit type casting or conversion functions. Here are two common approaches:

Explicit type casting: Use the CAST keyword to explicitly cast a value to a different data type, as in the following CAST(column_name AS <new_data_type>).
Conversion functions: PostgreSQL provides various conversion functions. For example, :: is a shorthand for the CAST syntax (E.g., column_name::<new_data_type>).

What is the difference between VARCHAR and TEXT in PostgreSQL?

Both VARCHAR and TEXT are used to store variable-length character strings. The primary difference lies in their historical usage and potential storage optimizations. VARCHAR has a specified length limit, while TEXT is unrestricted. However, in modern PostgreSQL, the performance and storage differences between the two are negligible, and they are often used interchangeably.

What are the PostgreSQL data types that come from the SQL specification?

These are the built-in PostgreSQL data types coming from the standard SQL: BIGINT, BIT, BIT VARYING, BOOLEAN, CHAR, CHARACTER VARYING, CHARACTER, VARCHAR, DATE, DOUBLE PRECISION, INTEGER, INTERVAL, NUMERIC, DECIMAL, REAL, SMALLINT, TIME, TIMESTAMP, XML.

Discover All PostgreSQL Data Types

What Is a Data Type in PostgreSQL?

PostgreSQL Data Types Cheat Sheet

PostgreSQL Data Type List

Numeric Types

Character Types

Binary Data Types

Date and Time Types

Boolean Types

XML Types

JSON Types

Array Types

Bit String Types

Text Search Types

Enumerated Types

Monetary Types

Geometric Types

Network Address Types

UUID Type

Composite Types

Range Types

Pseudo Types

Object Identifier Types

Conclusion

FAQ

Is there a database client that supports all PostgreSQL data types?

What is the difference between JSON and JSONB in Postgres?

How to convert one data type to another in PostgreSQL?

What is the difference between VARCHAR and TEXT in PostgreSQL?

What are the PostgreSQL data types that come from the SQL specification?

A Guide To the SQL DECLARE Statement

SQL Upsert: Inserting a Record If It Does Not Exist

How to Find Elements In an Array: PostgreSQL Guide

How and Why to Add an Index in SQL

Postgres Create Array of Numbers: Complete Guide

List of SQL Reserved Words in All Major Databases

SQL Variable: What It Is and How To Use It

Postgres DATEDIFF Equivalent Functions: Calculating Date and Time Differences

CREATE SCHEMA PostgreSQL Statement: What, Why & When to Use

SQL EXPLAIN: The Definitive Tool to Optimize Queries

Product

Partners

Legal

Company

Social