intro
Let's explore the data types supported by PostgreSQL, broken down into logical categories.
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:
| Name | Storage size | Description |
|---|---|---|
| BOOLEAN | 1 byte | State of true, false, or unknown |
Notes:
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:
1
CREATE TABLE achievements (
2
user_id integer,
3
trophies **integer[]**,
4
);
Otherwise, you can use the keyword ARRAY to set the array length:
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:
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:
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:
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:
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!
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:
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.
