PostgreSQL is a powerful open-source relational database management system that offers a wide range of data types for storing and manipulating data. Understanding PostgreSQL data types is essential for creating efficient and effective database designs. This cheat sheet provides a quick reference guide to all of the data types supported by PostgreSQL.
PostgreSQL data types can be divided into several categories, including:
- Numeric data types, such as integers, floating-point numbers, and decimal numbers.
- Character data types, such as strings and text.
- Date and time data types, such as timestamps and intervals.
- Boolean data types, which represent logical values.
- Geometric data types, which represent points, lines, and other geometric shapes.
- Network address data types, which represent IP addresses and subnets.
- Array data types, which allow for the storage of arrays of values.
- Other specialized data types, such as full-text search data types and JSON data types.
- Each data type has its own unique set of features and storage requirements. Understanding the differences between these data types can help you choose the right one for your specific needs.
This cheat sheet provides a quick reference guide to all of the data types supported by PostgreSQL. Each data type is listed along with a brief description of its features and usage. It’s important to note that the actual storage size and precision of these data types may vary based on the platform and specific configuration of PostgreSQL.
Cheat Sheet
| Data Type | Description |
| bigint | A signed eight-byte integer (-9223372036854775808 to 9223372036854775807). |
| bigserial | An auto-incrementing eight-byte integer (1 to 9223372036854775807). |
| bit [ (n) ] | A fixed-length bit string. |
| bit varying [ (n) ] or varbit [ (n) ] | A variable-length bit string. |
| boolean | A boolean value of either TRUE or FALSE. |
| box | A rectangular box in a two-dimensional plane. |
| bytea | A variable-length binary string. |
| character [ (n) ] or char [ (n) ] | A fixed-length string of n characters. |
| character varying [ (n) ] or varchar [ (n) ] | A variable-length string of up to n characters. |
| cidr | A network IP address represented as an IPv4 or IPv6 address with an optional subnet mask. |
| circle | A circle in a two-dimensional plane. |
| date | A calendar date. |
| double precision | An eight-byte floating-point number. |
| inet | A network IP address represented as an IPv4 or IPv6 address. |
| integer | A signed four-byte integer (-2147483648 to 2147483647). |
| interval [ fields ] [ (p) ] | A time span with a precision of p decimal places, with fields indicating the units of the interval (e.g., YEAR, MONTH, DAY, HOUR, etc.). |
| json | A variable-length JSON string. |
| jsonb | A binary format for storing JSON data. |
| line | An infinite line in a two-dimensional plane. |
| lseg | A line segment in a two-dimensional plane. |
| macaddr | A six-byte MAC address. |
| money | A currency amount with a fixed precision of two decimal places. |
| numeric [ (p, s) ] or decimal [ (p, s) ] | A fixed-point number with p total digits and s decimal places. |
| path | A geometric path in a two-dimensional plane. |
| point | A point in a two-dimensional plane. |
| polygon | A closed geometric shape with three or more sides in a two-dimensional plane. |
| real | A four-byte floating-point number. |
| smallint | A signed two-byte integer (-32768 to 32767). |
| smallserial | An auto-incrementing two-byte integer (1 to 32767). |
| serial | An auto-incrementing four-byte integer (1 to 2147483647). |
| text | A variable-length string of up to one gigabyte. |
| time [ (p) ] [ without time zone ] | A time of day with a precision of p decimal places. |
| time [ (p) ] with time zone | A time of day with a precision of p decimal places, with a time zone offset. |
| timestamp [ (p) ] [ without time zone ] | A date and time with a precision of p decimal places. |
| tsquery | A query for full-text search. |
| tsvector | A document in full-text search format. |
| txid_snapshot | A transaction ID snapshot for use with transaction visibility functions. |
| uuid | A universally unique identifier (UUID). |
| xml | A variable-length XML string. |
Note: Some data types, such as point, line, lseg, box, path, polygon, and circle, are considered geometric data types and can be used with geometric functions in PostgreSQL.
Additionally, some data types, such as json and jsonb, are used for storing JSON data, while others, such as tsquery and tsvector, are used for full-text search.
Reference: