The PostgreSQL is a database that holds different values of data. So in this tutorial let have a look over the PostgreSQL data types. But before we were going to discuss the PostgreSQL supported data types, I would like to recall you once again
What is the data type?
A data type (or) simply types refers to the specific type of data that the variable holds. Moreover, this include date, timestamps, varchar, and some other formats.
Let us have a look at the PostgreSQL datatypes.
Numeric Datatypes :
PostgreSQL supports two types of Numeric datatypes namely integers and Floating point numbers. So let us now discuss the PostgreSQL supports types with range.
| Name | Storage size | Range |
| Small | 2 bytes | -32768 - +32767 |
| Integer | 4 bytes | -2147483648 to +2147483647 |
| Bigint | 8 bytes | -9223372036854775808 to 9223372036854775807 |
| Real | 4 bytes | It support 06 digits precision |
| Double precision | 8 bytes | It supports 15digit decimal precision |
| Decimal | Variable | It permits up to 131072 before the decimal point, up to 16383 after the decimal point |
| Numeric | Variable | It permits up to 131072 before the decimal point, up to 16383 after the decimal point |
| Small serial | 2 bytes | 1 – 32767 |
| Serial | 4 bytes | 1 - 2147483647 |
| Double serial | 8 bytes | 1 to 9223372036854775807 |
Monetary data types :
This is the special data that the database supports. Here in this data type, values of numeric, int and bigint can be converted into money. So today many people say that we can use Float datatype. But this is not recommended to handle money due to the potential for rounding errors.
| Name | Storage | Range |
| Money | 8 bytes | -92233720368547758.08 to +92233720368547758.07 |
Character data types:
The following table contains the PostgreSQL character datatypes.
| Name | Description |
| Char(size)/ Character(size) | Here size is the number of characters to store. It contains the fixed length strings. Here the space padded on right to equal size characters. |
| Varchar(size) | Here size is the number of characters to store. It contains the variable string length |
Date/time data type:
The date/time datatype is used to represent the columns using the date and time values.
| Name | size | Range | Resolution |
| Timestamp(with/ without timezone) | 8 bytes | 4713 BC to 294276 AD | 1 microsecond / 14 digits |
| Date | 4bytes | 4713 BC to 294276 AD | One day |
| Time without time zone | 8 bytes | 00:00:00 to 24:00:00 | 1 microsecond /14 digits |
| Timewith timezone | 12 bytes | 00:00:00 + 1459 to 24:00:00-1459 | I microsecond/ 14 digits |
| Interval | 12bytes | -178000000 to 178000000 years | I microsecond/14 digits |
Geometric data types :
The geometric data types represent two-dimensional data objects. Moreover these data types help to perform a various operation like rotation, scaling and translation etc.
| Name | Storage | Representation | Description |
| Point | 16 bytes | Point on a plane | (x,y) |
| Line | 32 bytes | Iinfinte line | ((x1.y1).(x2.y2)) |
| Line segment | 32 bytes | Finite line segment | ((x1.y1).(x2.y2)) |
| Box | 32 bytes | Rectangular box | ((x1.y1).(x2.y2)) |
| Path | 16 n + 16 n bytes | Close and open path | ((x1.y1),…..) |
| Polygon | 40 + 16 n bytes | Polygon | [(x1.y1)…] |
| Circle | 24 bytes | Circle | <(x.y).r> |
Network Address type:
Today we do have many network address types like IPV4, IP V6, and MAC address. Moreover, this PostgreSQL offers different data types to store in these address. To store in network address, instead of plain text, it is preferable to store these data types.
| Name | Storage size | Description |
| Cidr | 7 (or) 19 bytes | IPV4 and IPV6 networks |
| Inet | 7 (or) 19 bytes | IPV4 and IPV 6 hosts and networks |
| Macaddr | 6 bytes | Mac address |
Enumerated data types:
In PostgreSQL, enumerated data types are useful for representing the rarely changing information such as country code (or) branch id. so to ensure data integrity, the enumerated data type is represented with a table with a foreign key.
Range type :
These represent data that uses the data range. These range types can be a discrete range (or) continuous ranges. Postgre SQL contains the following built-in data types.
| Name | Description |
| Int4range | Range of the integer |
| Int8range | Range of the big int |
| Numrange | Range of the numeric |
| tsrange | Without timezone, time stamp range |
| tstzrange | With timezone, time stamp range |
| Date range | It represents the range of the date |
Pseudo types :
PostgreSQL contains a number of special purpose entries that are collectively called as pseudo types. Moreover , a pseudo data type cannot be used as a column data type that can be used as a column data type. Additionally, this can be used to declare a function argument (or) a result type.
| Name | Description |
| Any | It indicates the function accepts any input data type |
| Any element | It indicates the function accepts any data type |
| Any array | It indicates the function accepts any array data type |
| Anynonarray | It indicates the function accepts any non-array data type |
| Anyenum | It indicates the function accepts any enum data type |
| Anyrange | It indicates the function accepts any range data type. |
| Internal | It indicates the function accepts (or) returns an internal server data. |
| Record | It indicates the function returning an unspecified row type |
| Void | It indicates the function that has no value |
| CString | It indicates the function accepts (or) returns the Null-terminated C string |