DATA TYPES
POSTGRESQL

Discover All PostgreSQL Data Types

intro

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

Tools used in the tutorial
Tool Description Link
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT
PostgreSQL logo POSTGRESQL
THE POSTGRESQL DATABASE

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:

NameDescription
BIGINTSigned eight-byte integer
BIGSERIALAuto-incrementing eight-byte integer
BITFixed-length bit string
BIT VARYINGVariable-length bit string
BOOLEANLogical boolean (true or false)
BOXRectangular box on a plane
BYTEABinary data
CHARACTERFixed-length character string
CHARACTER VARYINGVariable-length character string
CIDRIPv4 or IPv6 network address
CIRCLECircle on a plane
DATECalendar date (year, month, day)
DOUBLE PRECISIONDouble precision floating-point eight-byte number
INETIPv4 or IPv6 host address
INTEGERSigned four-byte integer
INTERVALTime span
JSONTextual JSON data
JSONBBinary JSON data
LINEInfinite line on a plane
LSEGLine segment on a plane
MACADDRMAC address
MACADDR8MAC address (EUI-64 format)
MONEYCurrency amount
NUMERICExact numeric of selectable precision
PATHGeometric path on a plane
PG_LSNPostgreSQL LSN (Log Sequence Number)
PG_SNAPSHOTUser-level transaction ID snapshot
POINTGeometric point on a plane
POLYGONClosed geometric path on a plane
REALSingle precision floating-point four-byte number
SMALLINTSigned two-byte integer
SMALLSERIALAuto-incrementing two-byte integer
SERIALAuto-incrementing four-byte integer
TEXTVariable-length character string
TIMETime of day (with or without time zone)
TIMESTAMPDate and time (with or without time zone)
TSQUERYText search query
TSVECTORText search document
UUIDUniversally unique identifier
XMLXML 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.

NameStorage sizeDescriptionRange
SMALLINT2 bytesSmall-range integer-32768 to +32767
INTEGER4 bytesMost common choice for integer data-2147483648 to +2147483647
BIGINT8 bytesLarge-range integer-9223372036854775808 to +9223372036854775807
DECIMALVariableUser-specified precision• Up to 131072 digits before the decimal point
• Up to 16383 digits after the decimal point
NUMERICVariableUser-specified precision• Up to 131072 digits before the decimal point
• Up to 16383 digits after the decimal point
REAL4 bytesVariable-precision6 decimal digits precision
DOUBLE PRECISION8 bytesVariable-precision15 decimal digits precision
SMALLSERIAL2 bytesSmall auto-incrementing integer1 to 32767
SERIAL4 bytesAuto-incrementing integer1 to 2147483647
BIGSERIAL8 bytesLarge Auto-incrementing integer1 to 9223372036854775807

Character Types

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

NameDescription
CHARACTER VARYING(N), VARCHAR(N)Variable-length with limit
CHARACTER(N), CHAR(N), BPCHAR(N)Fixed-length, blank-padded
BPCHARVariable unlimited length, blank-trimmed
TEXTVariable 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).

NameStorage sizeDescription
BYTEA1 or 4 bytes plus the actual binary stringVariable-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.

NameStorage sizeDescriptionLowest valueHighest valueResolution
TIMESTAMP [ (P) ] [ WITHOUT TIME ZONE ]8 bytesBoth date and time (no time zone)4713 BC294276 AD1 microsecond
TIMESTAMP [ (P) ] WITH TIME ZONE8 bytesBoth date and time, with time zone4713 BC294276 AD1 microsecond
DATE4 bytesDate (no time of day)4713 BC5874897 AD1 day
TIME [ (P) ] [ WITHOUT TIME ZONE ]8 bytesTime of day (no date)00:00:0024:00:001 microsecond
TIME [ (P) ] WITH TIME ZONE12 bytesTime of day (no date), with time zone00:00:00+155924:00:00-15591 microsecond
INTERVAL [ FIELDS ] [ (P) ]16 bytesTime interval-178000000 years178000000 years1 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

  • TRUE: “true” state.
  • FALSE: “false” state.
  • NULL: “unknown” state.
NameStorage sizeDescription
BOOLEAN1 byteState 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:

NameDescription
XMLString data in XML format

JSON Types

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

NameDescription
JSONJSON string data in the exact copy of the input text
JSONBJSON 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.

NameDescription
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.

NameDescription
TSVECTORSorted list of distinct lexemes
TSQUERYString 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.

NameStorage sizeDescriptionRange
MONEY8 bytesCurrency amount-92233720368547758.08 to +92233720368547758.07

Geometric Types

Geometric data types represent two-dimensional spatial objects.

NameStorage sizeDescriptionRepresentation
POINT16 bytesPoint on a plane(x,y)
LINE32 bytesInfinite line{A,B,C}
LSEG32 bytesFinite line segment((x1,y1),(x2,y2))
BOX32 bytesRectangular box((x1,y1),(x2,y2))
PATH16+16n bytesBoth open or closed path[(x1,y1),...]
POLYGON40+16n bytesPolygon (similar to closed path)((x1,y1),...)
CIRCLE24 bytesCircle<(x,y),r> (center point and radius)

Network Address Types

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

NameStorage sizeDescription
CIDR7 or 19 bytesIPv4 and IPv6 networks
INET7 or 19 bytesIPv4 and IPv6 hosts and networks
MACADDR6 bytesMAC addresses
MACADDR88 bytesMAC 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.

NameDescription
UUID128-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 typeDescription
ANYIndicates that a function accepts any input data type
ANYELEMENTIndicates that a function accepts any data type
ANYARRAYIndicates that a function accepts any array data type
ANYNONARRAYIndicates that a function accepts any non-array data type
ANYENUMIndicates that a function accepts any enum data type
ANYRANGEIndicates that a function accepts any range data type
ANYMULTIRANGEIndicates that a function accepts any multirange data type
ANYCOMPATIBLEIndicates that a function accepts any data type, with automatic promotion of multiple arguments to a common data type
ANYCOMPATIBLEARRAYIndicates that a function accepts any array data type, with automatic promotion of multiple arguments to a common data type
ANYCOMPATIBLENONARRAYIndicates that a function accepts any non-array data type, with automatic promotion of multiple arguments to a common data type
ANYCOMPATIBLERANGEIndicates that a function accepts any range data type, with automatic promotion of multiple arguments to a common data type
ANYCOMPATIBLEMULTIRANGEIndicates that a function accepts any multirange data type, with automatic promotion of multiple arguments to a common data type
CSTRINGIndicates that a function accepts or returns a null-terminated C string
INTERNALIndicates that a function accepts or returns a server-internal data type
LANGUAGE_HANDLERA procedural language call handler is declared to return LANGUAGE_HANDLER
FDW_HANDLERA foreign-data wrapper handler is declared to return FDW_HANDLER
TABLE_AM_HANDLERA table access method handler is declared to return TABLE_AM_HANDLER
INDEX_AM_HANDLERAn index access method handler is declared to return INDEX_AM_HANDLER
TSM_HANDLERA tablesample method handler is declared to return TSM_HANDLER
RECORDIdentifies a function taking or returning an unspecified row type
TRIGGERA trigger function is declared to return TRIGGER
EVENT_TRIGGERAn event trigger function is declared to return EVENT_TRIGGER
PG_DDL_COMMANDIdentifies a representation of DDL commands that is available to event triggers
VOIDIndicates that a function returns no value
UNKNOWNIdentifies 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.

NameReferencesDescriptionSample value
OIDANYNumeric object four-byte identifier536792
REGCLASSPG_CLASSRelation namepg_type
REGCOLLATIONPG_COLLATIONCollation name"POSIX"
REGCONFIGPG_TS_CONFIGText search configurationenglish
REGDICTIONARYPG_TS_DICTText search dictionarysimple
REGNAMESPACEPG_NAMESPACENamespace namepg_catalog
REGOPERPG_OPERATOROperator name-
REGOPERATORPG_OPERATOROperator with argument types*(INTEGER,INTEGER) or -(NONE,INTEGER)
REGPROCPG_PROCFunction namesum
REGPROCEDUREPG_PROCFunction with argument typessum(INT4)
REGROLEPG_AUTHIDRole nameadmin
REGTYPEPG_TYPEData type nameINTEGER

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
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:

  1. 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>).
  2. 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.

Dbvis download link img
About the author
Antonello Zanini

Antonello is a software engineer, and often refers to himself as a technology bishop. His mission is to spread knowledge through writing.

The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

SQL Alias: Everything You Need to Know About AS in SQL

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 7 min 2024-07-15
title

How to Compare SQL Dates

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 4 min 2024-07-11
title

REGEXP_MATCH SQL Function: The Complete PostgreSQL Guide

author Antonello Zanini tags POSTGRESQL REGEXP SQL 8 min 2024-07-04
title

Primary Key vs. Foreign Key: A Complete Comparison

author Lukas Vileikis tags MySQL POSTGRESQL SQL SQL SERVER 7 min 2024-06-20
title

A Complete Guide to the SQL LIKE Operator

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 8 min 2024-06-17
title

A Definitive Guide to Postgres Foreign Key

author Leslie S. Gyamfi tags POSTGRESQL SQL 12 min 2024-06-13
title

A Guide to the SQL Date Data Types

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 10 min 2024-05-30
title

SQL TRIM: Removing Extra Space Characters From a String

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 6 min 2024-05-16
title

PostgreSQL Full Text Search: The Definitive Guide

author Antonello Zanini tags POSTGRESQL Search 11 min 2024-05-13
title

SQL UNION ALL: Keeping Duplicates When Combining Result Sets

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL UNION 8 min 2024-05-09

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗