PostgreSQL Data Types
It is necessary to specify the type of data that can be stored and processed in a PostgreSQL database along with the specification of the type of operations that can be performed on that type of data. This purpose is served by the various data types supported by the PostgreSQL database including integer data type, floating point data type, string data type, boolean data type, etc. PostgreSQL supports a lot of the standard data types of SQL which are divided into various categories.
Numeric Data Types:
There are mainly Nine numeric data types that PostgreSQL supports. These are listed below:
| Data Type | Range | Storage Size | Description |
| INTEGER | -2147483648 to 2147483647 (signed)
0 to 4294967295 (unsigned) |
4 Bytes | Normal sized integer value. |
| SMALLINT | -32768 to 32767 (signed)
0 to 65535 (unsigned) |
2 Bytes | Small integer value. |
| BIGINT | -9223372036854775808 to 9223372036854775807 (signed)
0 to 18446744073709551615 (unsigned) |
8 Bytes | Large integer value. |
| DOUBLE
PRECISION |
15 Decimal digits precision. | 8 Bytes | Variable precision floating point number. |
| DECIMAL | 131072 before decimal; 16383 after decimal | variable | User Specified precision fixed point number. |
| NUMERIC | 131072 before decimal; 16383 after decimal | variable | User Specified precision fixed point number. |
| REAL | 6 Decimal digits precision. | 4 Bytes | Variable precision floating point number. |
| SERIAL | 1 to 2147483647 | 4 Bytes | Auto Incrementing Integer. |
| BIGSERIAL | 1 to 9223372036854775807 | 8 Bytes | Large Auto Incrementing Integer |
Date and Time Data Types:
There are mainly five date and time data types that PostgreSQL supports. These are listed below:
| Data Type | Size | Range | Resolution | Description |
| TIMESTAMP [ (p) ] [ without time zone ] | 8 Bytes | 4713 BC to 294276 AD | 1 microsecond / 14 digits | Both date and time |
| TIMESTAMP [ (p) ] [with time zone] | 8 Bytes | 4713 BC to 294276 AD | 1 microsecond / 14 digits | Both date and time |
| DATE | 4 Bytes | 4713 BC to 5874897 AD | 1 day | Date (no time of day) |
| TIME [ (p) ] [ without time zone ] | 8 Bytes | 00:00:00 to 24:00:00 | 1 microsecond / 14 digits | Time of day (no date) |
| TIME [ (p) ] [with time zone] | 12 Bytes | 00:00:00+1459 to 24:00:00-1459 | 1 microsecond / 14 digits | Time of day (no date) |
| INTERVAL [ fields ] [ (p) ] | 12 Bytes | -178000000 years to 178000000 years | 1 microsecond / 14 digits | Time interval |
String Data Types:
There are mainly five string data types that PostgreSQL supports. These are listed below:
| Data Type | Maximum Size | Description |
| CHAR(size) | 255 characters | Here, size is equal to the number of characters to store. It is used for fixed-length strings with space padded on right to equal size characters. |
| VARCHAR(size) | 255 characters | Here, size is equal to the number of characters to store. It is used for variable-length strings. |
| TEXT(size) | 65,535 characters | Here, size is equal to the number of characters to store. |
| CHARACTER(size) | 255 characters | Here, size is equal to the number of characters to store. It is used for fixed-length strings with space padded on right to equal size characters. |
| CHARACTER VARYING(size) | 255 characters | Here, size is equal to the number of characters to store. It is used for variable-length strings. |
Boolean Data Types:
There are mainly one boolean data types that PostgreSQL supports.
| Data Type | Size | Description |
| BOOLEAN | 1 Byte | Specifies the state of True or False. |
Monetary Data Types:
There are mainly one monetary data types that PostgreSQL supports.
| Data Type | Size | Range | Description |
| MONEY | 8 Bytes | -92233720368547758.08 to +92233720368547758.07 | Specifies the Currency Amount. |
Geometric Data Types:
There are mainly eight geometric data types that PostgreSQL supports. These are listed below:
| Data Type | Size | Description |
| POINT | 16 Bytes | Point on a plane. |
| LINE | 32 Bytes | Infinite line. |
| LSEG | 32 Bytes | Finite line segment. |
| BOX | 32 Bytes | Rectangular box. |
| PATH | 16+16n Bytes | Closed path. |
| PATH | 16+16n Bytes | Open path |
| POLYGON | 40+16n Bytes | Polygon. |
| CIRCLE | 24 Bytes | Circle. |
