Introduction
PostgreSQL is an open-source relational database management system known for its robustness and extensibility. This also means PostgreSQL offers a variety of data types for users.
In this tutorial, we will cover the different built-in data types available in PostgreSQL.
PostgreSQL Data Types
When creating a table in PostgreSQL, you can specify a data type for each column. PostgreSQL supports a wide range of built-in data types:
Note: You can create custom data types in PostgreSQL using the CREATE TYPE SQL command.
Character
PostgreSQL uses character data types for storing text values. There are three character data types in PostgreSQL:
| Name | Description |
| character(n), char(n) | Fixed-length strings, where n is the number of characters. Blank space padded on the right to equal n. |
| character varying(n), varchar(n) | Variable-length string with a character limit, where n is the number of characters. |
| text | Variable-length, unlimited string. |
Numeric types
Numeric data types include:
- Two-, four-, and eight-byte integers
- Four- and eight-byte floating point numbers
- Selectable decimals:
| Name | Storage Size | Description | Range |
| smallint | 2 bytes | Small-range integer. | -32768 to +32767 |
| integer | 4 bytes | Medium-range integer. | -2147483648 to +2147483647 |
| bigint | 8 bytes | Large-range integer. | -9223372036854775808 to 9223372036854775807 |
| decimal | variable | User-specified precision decimal. | Up to 131072 digits before the decimal point. Up to 16383 digits after the decimal point |
| numeric | variable | User-specified precision decimal. | up to 131072 digits before the decimal point. Up to 16383 digits after the decimal point |
| real | 4 bytes | Variable precision decimal. | 6 decimal digits precision |
| double precision | 8 bytes | Variable precision decimal. | 15 decimal digits precision |
| smallserial | 2 bytes | Small autoincrementing integer. | 1 to 32767 |
| serial | 4 bytes | Medium autoincrementing integer. | 1 to 2147483647 |
| bigserial | 8 bytes | Large autoincrementing integer. | 1 to 9223372036854775807 |
Monetary
The monetary data type stores a numeric amount of money with fixed fractional precision. This type stores up to 8 bytes of data with a range of -92233720368547758.08 to +92233720368547758.07 and use numeric, integer, and bigint data types as values.
Note: Using floating-point numbers with the monetary data type is not recommended due to the potential for rounding errors.
Date/Time
PostgreSQL Supports all the standard SQL date and time data types, with a resolution of 1 microsecond or 14 digits. Date is the only exception, with a resolution of one day, counted according to the Gregorian calendar:
| Name | Storage Size | Description | Range |
| timestamp | 8 bytes | Date and time, without time zone. | 4713 BC to 294276 AD |
| timestampz | 8 bytes | Date and time, with time zone. | 4713 BC to 294276 AD |
| date | 4 bytes | Date. | 4713 BC to 294276 AD |
| time without time zone | 8 bytes | Time of day, without time zone. | 00:00:00 to 24:00:00 |
| time with time zone | 12 bytes | Time of day, with time zone. | 00:00:00 + 1459 to 24:00:00-1459 |
| interval | 12 bytes | Time interval. | -178000000 to 178000000 years |
Binary
PostgreSQL can save variable-length binary strings as the bytea data type, taking 1 or 4 bytes plus the size of the actual binary string.
Boolean
A Boolean data type is declared using bool or boolean keywords. It can hold true (1), false (0), or unknown (null) values.
Enumerated
Enumerated data types consist of a static, ordered set of values, such as numbers from 1 to 10 or months in the year. Unlike other data types, you can create enumerated types using the create type command:
CREATE TYPE year AS ENUM ('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec');Bit String
Bit string type stores strings of 1’s and 0’s, used to store or visualize bit masks:
| Name | Description |
| bit(n) | Stores a bit string with a fixed length of n characters. |
| varying(n) | Stores a bit string of a varying length, up to n characters. |
UUID
A UUID (Universally Unique Identifier) is a set of 32 digits created by an algorithm. It consists of several groups of four, eight, and twelve digits, separated by hyphens:
Network Address
PostgreSQL uses network address data types to store IPv4, IPv6, and MAC addresses:
| Name | Storage Size | Description |
| cidr | 7 or 19 bytes | Stores IPv4 and IPv6 networks. |
| inet | 7 or 19 bytes | Stores IPv4 and IPv6 hosts and networks. |
| macaddr | 6 bytes | Stores MAC addresses. |
Using network address data types has several advantages over using plain text. This includes saving storage space, specialized functions and commands, and easier error checking.
Text Search
Text search data type allows you to search for the best match in a collection of natural-language documents:
| Name | Description |
| tsvector | Represents a document optimized for text search, with a list of distinct words normalized to merge different variants of the same word (lexemes). |
| tsquery | Stores the keywords that need to be searched and combines them using Boolean operators (AND, OR, and NOT). |
Geometric
Geometric data types represent spatial objects rendered in two dimensions, such as points, lines, and polygons:
| Name | Storage Size | Representation | Numerical Description |
| point | 16 bytes | Point on a plane. | (x,y) |
| line | 32 bytes | Infinite line. | ((x1,y1),(x2,y2)) |
| lseg | 32 bytes | Finite line segment. | ((x1,y1),(x2,y2)) |
| box | 32 bytes | Rectangular box. | ((x1,y1),(x2,y2)) |
| path | 16+16n bytes | Open or closed path. | ((x1,y1),…(xn,yn)) |
| polygon | 40+16n bytes | Polygon. | ((x1,y1),…(xn,yn)) |
| circle | 24 bytes | Circle. | ((x,y),r) (center point and radius) |
XML
PostgreSQL allows you to save XML data as an XML data type using the XMLPARSE function:
XMLPARSE (DOCUMENT [document name] WELLFORMED)or:
XMLPARSE (CONTENT [XML content] WELLFORMED)Where:
[document name]: A singly-rooted XML document.[XML content]: Valid XML valueWELLFORMED: This option guarantees that [document name] or [XML content] resolve to a well-formed XML document. Use it only when you don’t want the database to check if the input is well-formed.
JSON
PostgreSQL offers two JSON data types:
- json: An extension of the text data type with JSON validation. This data type saves data exactly the way it is (including whitespace). You can quickly insert it into the database, but it is relatively slow to retrieve due to reprocessing.
- jsonb: Represents JSON data in a binary format. Slower to insert into the database, but indexing support and lack of reprocessing make retrieval significantly faster.
Array
The array data type lets you define a column of a table as a multidimensional array that can use any base, enumerated, or composite data type. You can declare, modify, and search arrays as you would any other column in the database.
Composite
Composite data types allow you to use a row or record of a table as a data element. Similar to array data types, you can also declare, search, and modify composite values.
Range
Range data types use discreet or continuous ranges of other data types. Built-in range data types include:
| Name | Description |
| int4range | Range of medium-size integers. |
| int8range | Range of large integers. |
| numrange | Range of user-specified precision decimals. |
| strange | Range of times and dates without a time zone. |
| tstzrange | Range of times and dates with a time zone. |
| daterange | Range of dates. |
You can also create custom range types by using other data types as a basis.
Object Identifier
PostgreSQL uses object identifiers as primary key systems when performing specialized input and output operations:
| Name | References | Description |
| oid | any | Numeric object identifier. |
| regproc | pg_proc | Function name. |
| regprocedure | pg_proc | Function with argument types. |
| regoper | pg_operator | Operator name. |
| regoperator | pg_operator | Operator with argument types. |
| regclass | pg_class | Relation name. |
| regtype | pg_type | Data type name. |
| regconfig | pg_ts_config | Text search configuration. |
| regdictionary | pg_ts_dict | Text search dictionary. |
Pseudo Types
Pseudo types are a collection of special entries for declaring a function’s argument or result type:
| Name | Description |
| any | Function accepts any input data type. |
| anyelement | Function accepts any data type. |
| anyarray | Function accepts any array data type. |
| anynonarray | Function accepts any non-array data type. |
| anyenum | Function accepts any enumerated data type. |
| anyrange | Function accepts any range data type. |
| cstring | Function accepts or return a null-terminated C string. |
| internal | Function accepts or return server-internal data type. |
| language_handler | Function returns language handler. |
| fdw_handler | A foreign-data wrapper handler returns fdw_handler. |
| record | Finds a function that returns an unspecified row type. |
| trigger | A trigger function returns trigger. |
| void | Function returns no value. |
Conclusion
After reading this article, you should have a basic working knowledge of data types available in PostgreSQL.
Get started with PostgreSQL by checking out our guides to installing PostgreSQL on Windows and installing PostgreSQL on Ubuntu.
To learn how to create a database in PostgreSQL using different methods, make sure to read our article how to create a database in PostgreSQL.
