Postgresql Data Types Cheat Sheet

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 TypeDescription
bigintA signed eight-byte integer (-9223372036854775808 to 9223372036854775807).
bigserialAn 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.
booleanA boolean value of either TRUE or FALSE.
boxA rectangular box in a two-dimensional plane.
byteaA 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.
cidrA network IP address represented as an IPv4 or IPv6 address with an optional subnet mask.
circleA circle in a two-dimensional plane.
dateA calendar date.
double precisionAn eight-byte floating-point number.
inetA network IP address represented as an IPv4 or IPv6 address.
integerA 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.).
jsonA variable-length JSON string.
jsonbA binary format for storing JSON data.
lineAn infinite line in a two-dimensional plane.
lsegA line segment in a two-dimensional plane.
macaddrA six-byte MAC address.
moneyA 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.
pathA geometric path in a two-dimensional plane.
pointA point in a two-dimensional plane.
polygonA closed geometric shape with three or more sides in a two-dimensional plane.
realA four-byte floating-point number.
smallintA signed two-byte integer (-32768 to 32767).
smallserialAn auto-incrementing two-byte integer (1 to 32767).
serialAn auto-incrementing four-byte integer (1 to 2147483647).
textA 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 zoneA 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.
tsqueryA query for full-text search.
tsvectorA document in full-text search format.
txid_snapshotA transaction ID snapshot for use with transaction visibility functions.
uuidA universally unique identifier (UUID).
xmlA 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.