PostgreSQL Data Types
In PostgreSQL, a data type is a classification of data based on the type of value it represents. The data type of a column in a PostgreSQL table determines the type of data that can be stored in that column, as well as the operations that can be performed on the data.
PostgreSQL provides a wide variety of data types that can be used to store different kinds of data, such as numbers, text, dates, and more complex data structures like arrays, geometric shapes, and network addresses.
| Data Type | Description | Range of Values |
|---|---|---|
| bigint | 8-byte integer | -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 |
| bigserial | Autoincrementing 8-byte integer | 1 to 9,223,372,036,854,775,807 |
| bit | Fixed-length bit string | n/a |
| bit varying | Variable-length bit string | 0 to 2^(n)-1 bits, where n is the length of the string |
| boolean | Logical Boolean (true/false) value | true or false |
| box | Rectangular box (defined by two points: bottom-left and top-right) | N/A |
| bytea | Binary data ("byte array") | 0 to 1 GB |
| character | Fixed-length character string (deprecated; use "char" or "character(n)" instead) | N/A |
| character(n) | Fixed-length character string of length n | N/A |
| char | Fixed-length character string | N/A |
| cidr | IPv4 or IPv6 network address | IPv4: x.x.x.x/y where x is a number between 0 and 255 and y is a number between 0 and 32, IPv6: x:x:x:x:x:x:x:x/y where x is a hexadecimal number between 0 and FFFF and y is a number between 0 and 128 |
| circle | Circle (defined by a center point and a radius) | N/A |
| date | Calendar date (year, month, and day) | 4713 BC to 5874897 AD |
| double precision | Double-precision floating-point number (8 bytes) | Approximately 15 decimal digits |
| inet | IPv4 or IPv6 host address | IPv4: x.x.x.x where x is a number between 0 and 255, IPv6: x:x:x:x:x:x:x:x where x is a hexadecimal number between 0 and FFFF |
| integer | 4-byte integer | -2,147,483,648 to 2,147,483,647 |
| interval | Time interval | -178000000 years to 178000000 years |
| json | JSON data | 1 byte to 1 GB |
| jsonb | Binary JSON data | N/A |
| line | Infinite line (defined by two points) | N/A |
| lseg | Line segment (defined by two points) | N/A |
| macaddr | MAC (Media Access Control) address | 00:00:00:00:00:00 to FF:FF:FF:FF:FF:FF |
| money | Currency amount | -92233720368547758.08 to +92233720368547758.07 |
| numeric | Arbitrary-precision decimal number | -10^131071 to 10^131071 |
| path | Geometric path (defined by points connected by straight lines or circular arcs) | N/A |
| point | Point in a two-dimensional plane | N/A |
| polygon | Closed geometric shape (defined by a set of connected points) | N/A |
| real | Single-precision floating-point number (4 bytes) | -3.402823e38 to 3.402823e38 |
| smallint | 2-byte integer | -32768 to 32767 |
| smallserial | Autoincrementing 2-byte integer | 1 to 32767 |
| serial | Autoincrementing 4-byte integer | 1 to 2147483647 |
| text | Variable-length character string | No specified limit |
| time | Time of day (no date) | 00:00:00 to 24:00:00. |
| time with time zone | Time of day with time zone | N/A |
| timestamp | Date and time (no time zone) | 4713 BC to 294276 AD |
| timestamp with time zone | Date and time with time zone | N/A |
| tsquery | Text search query | N/A |
| tsvector | Text search document | N/A |
| txid_snapshot | Transaction ID snapshot | N/A |
| uuid | Universally unique identifier | N/A |
| xml | XML data | N/A |