This article provides a reference for all of the built-in data types available in PostgreSQL databases.
PostgreSQL Data Types
| Data Type | Description | Example |
|---|---|---|
| Boolean | TRUE/FALSE Boolean value | TRUE |
| Character (CHAR) | Fixed-length string of characters | ‘Hello there’ |
| Character Varying (VARCHAR) | Variable-length string of characters | ‘Hello there’ |
| Text | String of characters with unlimited length | ‘This text can be as long as you want it to be!’ |
| Smallint | Integer value ranging from -32768 to +32767 | 56 |
| Integer (INT) | Integer value ranging from -2147483648 to +2147483647 | 654321 |
| Bigint | Integer value ranging from -9223372036854775808 to +9223372036854775807 | 99999999999 |
| Decimal | Numeric value with a fixed point/decimal place | 3.09 |
| Numeric | Variable-precision numeric value | 3.1563 |
| Real | Floating point number (single-precision) | 3.14159 |
| Double precision | Floating point number (double-precision) | 3.141592653589793 |
| Date | Date value | ‘2031-04-12’ |
| Time | Time value | ’23:59:59′ |
| Timestamp | Combined date and time value | ‘2031-04-12 23:59:59’ |
| Interval | Time duration | ‘1 year 7 months’ |
| UUID | Universally unique identifier (UUID) version 4 | ’00b64075-5882-4065-9918-e26339a00eba’ |
| JSON | JavaScript Object Notation (JSON) data | ‘{“product”: “Tomato”, “quantity”: 13}’ |
| JSONB | Binary format of JSON data that allows for faster indexing and querying | ‘{“product”: “Tomato”, “quantity”: 13}’ |
| XML | XML document | ‘Tomato13’ |
| Array | Array of values of the same data type | ‘{1, 2, 3, 4}’ |
| Range | Range of values of the same data type | ‘[1, 10]’ |
| Bit | Fixed-length bit string | B’1010′ |
| Bit Varying | variable-length bit string | B’1010′ |
| BOX | Rectangular box defined by two points marking the top left and bottom right coordinates | ‘((0,0),(2,2))’ |
| CIDR | IPv4 or IPv6 network address | ‘192.168.0.1/24’; |
| CIRCLE | Circle defined by center point and a radius | ‘((0,0), 9)’ |
| LINE | Straight line defined by two points | ‘((0,0), (9,9))’ |
| LSEG | Line segment defined by two points | ‘((0,0), (9,9))’ |
| MACADDR | MAC address (6-byte) | ’60:FA:61:8E:70:F7′ |
| MACADDR8 | MAC address (8-byte) | ’60:FA:61:8E:70:F7:04:05′ |
| MONEY | Currency/monetary amount with fixed-point/decimal precision | ‘245.45’ |
| PATH | Geometric path consisting of one or more line segments | ‘((0,0),(5,5),(9,9))’ |
| POINT | Point on 2-dimensional plane | ‘(4, 7)’ |
| POLYGON | Closed shape consisting of three or more straight sides | polygon ‘((0,0),(0,1),(1,1),(1,0),(0,0))’ |
| SMALLSERIAL | Auto-incrementing 2-byte integer with a value of 1 to 32767 | 3 |
| SERIAL | Auto-incrementing 4-byte integer with a value of 1 to 2147483647 | 834 |
| BIGSERIAL | Auto-incrementing 8-byte integer with a value of 1 to 9223372036854775807 | 54675678678 |
| BYTEA | Variable-length binary data string | ‘\xDEADBEEF’ |
More PostgreSQL Data Types
These are the most common built-in PostgreSQL data types. You can also define custom data types, including composite and ENUM types.
You can also create custom or composite types, and enums
