What are the data types in PostgreSQL?
PostgreSQL is an open-source, object-relational database management system. It comprises of both object and relational models.
The datatypes in PostgreSQL can be categorized into:
- Built-in
- User-defined
1. Built-in data types
Built-in datatypes are primitive data types.
a. Numeric datatypes
Numeric datatypes include Integer types, Arbitrary precision numbers, Floating point numbers, and Serial types.
Integer types are whole numbers without any fractional or decimal part.
| Name | Size | Range |
|---|---|---|
| smallint | 2 bytes | -32768 to +32767 |
| integer | 4 bytes | -2147483648 to +2147483647 |
| bigint | 8 bytes | -9223372036854775808 to +9223372036854775807 |
Arbitrary precision numbers are used where precision and exactness are required.
We can use the format NUMERIC ( precision, scale ). For example, 20.143 can be specified as NUMERIC(5,3) where
is 5 and digits after the total digits i.e., precision is 3. decimal point i.e., scale
| Name | Size | Range |
|---|---|---|
| decimal | variable | Before the decimal point: at most 131072 digits; after decimal point: at most 16383 digits |
| numeric | variable | Before the decimal point: at most 131072 digits; after decimal point: at most 16383 digits |
Floating point numbers also include decimal numbers, but without exactness or precision. They determine the maximum precision and are dependent on the architecture of the system.
| Name | Size | Range |
|---|---|---|
| real | 4 bytes | precision of 6 decimal digits |
| double precision | 8 bytes | precision of 15 decimal digits |
Serial types are special types of integers that have the auto-increment property used to create unique identifiers.
| Name | Size | Range |
|---|---|---|
| smallserial | 2 bytes | 1 to 32767 |
| serial | 4 bytes | 1 to 2147483647 |
| bigserial | 8 bytes | 1 to 9223372036854775807 |
b. Monetary type
The money type is typically used to store currency with a precision of the fractional part fixed.
Input is accepted as floating-point literals, currency, and integers.
| Name | Size | Range |
|---|---|---|
| money | 8 bytes | -92233720368547758.08 to +92233720368547758.07 |
c. Character types
Fixed length and variable length characters and strings can be stored in character types.
| Name | Description |
|---|---|
| varchar(n) | variable length with n chars as limit |
| char(n) | fixed length of n chars |
| text | unlimited variable length |
d. Date & time types
As indicated by the name, these are used to store the date and time.
| Name | Size |
|---|---|
| timestamp [ ( p ) ] [without time zone] | 8 bytes |
| timestamp [ ( p ) ] with time zone | 8 bytes |
| date | 4 bytes |
| time [ ( p ) ] [without time zone] | 8 bytes |
| time [ ( p ) ] with time zone | 12 bytes |
| interval [ fields ] [ ( p ) ] | 16 bytes |
fields must include SECONDS. The range of precision i.e. p is from 0 to 6.
e. Boolean type
Boolean Type is used to store the states of True, False, or Null.
| Name | Size |
|---|---|
| boolean | 1 byte |
f. UUID type
Uuid is a 16-byte number used to uniquely identify some information. This type is a 32-digit number with the format:
[8 digits]-[4 digits]-[4 digits]-[4 digits]-[12 digits]
g. Arrays
Array is created of either built-in or user-defined datatype.
Multidimensional arrays of variable lengths can also be defined as columns for a table in PostgreSQL.
even_numbers integer ARRAY,
h. JSON types
json stores data in the same format, but jsonb remove the whitespaces, does not maintain key ordering, and stores the data in binary for faster processing.
i. XML type
XML standard documents and content fragments are stored in the XML Type.
XMLPARSE ( { DOCUMENT | CONTENT } value);
j. Other built-in types
There are many other special data types related to network, geometric, etc. Some examples include box, line, point, polygon, inet and macaddr.
2. User-defined data type
User-defined data types include non-primitive data types created by the user by combining primitive data types.
CREATE TYPE creates a new user-defined data type in the database.
a. Enumerated Type
Enumerated Type includes ordered and static sets of values.
CREATE TYPE weekdays AS ENUM ( 'monday', 'tuesday', 'wednesday', 'thursday', 'friday' );
b. Other user-defined types
There are many other user-defined data types such as Composite type, Range type, Base type, and Array Type.
