PostgreSQL - Data Types
A Data Type is an attribute which specifies the type of data that will be stored inside each column when creating a table. The data type is a guideline for PostgreSQL to understand what type of data is expected inside of each column, and it also identifies how PostgreSQL will interact with the stored data.
In PostgreSQL, the data types can be mainly classified into four categories:
- String Data Types
- Numeric Data Types
- Date and time Data Types
- Other Data Types
String Data Types
| Data type | Description |
|---|---|
| CHARACTER(size) | A fixed-length character string. The size parameter specifies the number of characters to store. Space padded on right to equal size characters. Default value 1. |
| CHAR(size) | Synonym for CHARACTER(size) |
| CHARACTER VARYING(size) | A variable-length character string with limit. The size parameter specifies the number of characters to store. Default value 1. |
| VARCHAR(size) | Synonym for CHARACTER VARYING(size) |
| TEXT | A variable-length character string with unlimited length |
Numeric Data Types
| Data type | Description |
|---|---|
| BIT(size) | A fixed-length bit string. The number of bits per value is specified in size. Default is 1 |
| BIT VARYING(size) | A variable-length bit string. The number of bits per value is specified in size. Default is 1 |
| VARBIT(size) | Synonym for BIT VARYING(size) |
| SMALLINT | Signed two-byte integer. Range is from -32768 to +32767 |
| INTEGER | Signed four-byte integer. Range is from -2147483648 to +2147483647 |
| BIGINT | Signed eight-byte integer. Range is from -9223372036854775808 to +9223372036854775807 |
| INT2 | Synonym for SMALLINT |
| INT | Synonym for INTEGER |
| INT4 | Synonym for INTEGER |
| INT8 | Synonym for BIGINT |
| NUMERIC(size, d) | An exact fixed-point number. The total number of digits is specified in size. The number of digits after the decimal point is specified in the d parameter. |
| DECIMAL(size, d) | Synonym for NUMERIC(size, d) |
| DOUBLE PRECISION | 8 bytes variable-precision, inexact 15 decimal digits precision |
| REAL | 4 bytes variable-precision, inexact 6 decimal digits precision |
| SMALLSERIAL | Auto-incrementing two-byte integer. Range is from 1 to 32767 |
| SERIAL | Auto-incrementing four-byte integer. Range is from 1 to 2147483647 |
| BIGSERIAL | Auto-incrementing eight-byte integer. Range is from 1 to 9223372036854775807 |
| SERIAL2 | Synonym for SMALLSERIAL |
| SERIAL4 | Synonym for SERIAL |
| SERIAL8 | Synonym for BIGSERIAL |
| MONEY | Currency amount. Range is from -92233720368547758.08 to +92233720368547758.07 |
| BOOL | Logical Boolean (true/false) |
| BOOLEAN | Equal to BOOL |
Date and Time Data Types
| Data type | Description |
|---|---|
| DATE | A calendar date (year, month, day). Format: 'YYYY-MM-DD'. |
| TIMESTAMP(fsp) | A date and time with no time zone. Format: 'YYYY-MM-DD HH:MM:SS'. |
| TIMESTAMP(fsp) WITHOUT TIME ZONE | A date and time with no time zone. Format: 'YYYY-MM-DD HH:MM:SS'. |
| TIMESTAMP(fsp) WITH TIME ZONE | A date and time with no time zone. Format: 'YYYY-MM-DD HH:MM:SS-TZ'. Equivalent to TIMESTAMPTZ. |
| TIME(fsp) | A time of day with no time zone. Format: 'HH:MM:SS'. |
| TIME(fsp) WITHOUT TIME ZONE | A time of day with no time zone. Format: 'HH:MM:SS'. |
| TIME(fsp) WITH TIME ZONE | A time of day with time zone. Format: 'HH:MM:SS-TZ'. Equivalent to TIMETZ. |
| INTERVAL [FIELDS] (fsp) | Time span |
Other Data Types
| Data type | Description |
|---|---|
| pg_lsn | PostgreSQL Log Sequence Number |
| bytea | binary data (“byte array”) |
| box | rectangular box on a plane |
| cidr | IPv4 or IPv6 network address |
| circle | circle on a plane |
| inet | IPv4 or IPv6 host address |
| json | textual JSON data |
| jsonb | binary JSON data, decomposed |
| line | infinite line on a plane |
| lseg | line segment on a plane |
| macaddr | MAC (Media Access Control) address |
| macaddr8 | MAC (Media Access Control) address (EUI-64 format) |
| path | geometric path on a plane |
| pg_snapshot | user-level transaction ID snapshot |
| point | geometric point on a plane |
| polygon | closed geometric path on a plane |
| tsquery | text search query |
| tsvector | text search document |
| txid_snapshot | user-level transaction ID snapshot (deprecated; see pg_snapshot) |
| uuid | universally unique identifier |
| xml | XML data |
