PostgreSQL provides a variety of Data Types such as Numeric Types, Character Types, Date/Time Types, Boolean Types, and many more, which are used to store various types of user data. User can create their custom data types using the CREATE TYPE SQL. Defining the correct Data types provides several benefits such as operations on the same data types providing the best performance, the correct usage of data types providing the proper validation of data, and rejecting the unnecessary data, the storage can be utilized properly if we define the correct format of Data Types which results in the best performance.
The following is the list of Data Types which are supported in PostgreSQL.
- Numeric Types
- Monetary Types
- Character Types
- Binary Data Types
- Date/Time Types
- Boolean Type
- Enumerated Types
- Geometric Types
- Network Address Types
- Bit String Types
- Text Search Types
- UUID Type
- XML Type
- JSON Types
- Arrays
- Composite Types
- Range Types
- Domain Types
- Object Identifier Types
- pg_lsn Type
- Pseudo-Types
Let's explore the PostgreSQL Data Types in the below section.
Numeric Data Types
The PostgreSQL Numeric Data Types stores the two-byte, four-byte, and eight-byte integers and it stores the four-byte and eight-byte floating-point numbers and the selectable-precision decimals.
| Data Type Name | Data Type Storage Size | Data Type Description | Data Type Range |
|---|---|---|---|
| smallint | 2 bytes | small-range integer | From -32768 to +32767 |
| integer | 4 bytes | typical choice for integer | From -2147483648 to +2147483647 |
| bigint | 8 bytes | large-range integer | From -9223372036854775808 to +9223372036854775807 |
| decimal | variable | user-specified precision, exact | up to 131072 digits before the decimal point; up to 16383 digits after the decimal point |
| numeric | variable | user-specified precision, exact | up to 131072 digits before the decimal point; up to 16383 digits after the decimal point |
| real | 4 bytes | variable-precision, inexact | 6 decimal digits precision |
| double precision | 8 bytes | variable-precision, inexact | 15 decimal digits precision |
| smallserial | 2 bytes | small autoincrementing integer | From 1 to 32767 |
| serial | 4 bytes | autoincrementing integer | From 1 to 2147483647 |
| bigserial | 8 bytes | large autoincrementing integer | From 1 to 9223372036854775807 |
Monetary Data Types
The PostgreSQL Monetary Data Types stores the currency information in a fixed fractional precision. The casting of numeric, int, and bigint data types can be done in the money data type. Due to its routing error, it is not recommended to use the Floating point numbers for handling the money.
| Data Type Name | Data Type Storage Size | Data Type Description | Data Type Range |
|---|---|---|---|
| money | 8 bytes | currency amount | From -92233720368547758.08 to +92233720368547758.07 |
Character Data Types
The PostgreSQL Character Data Types stores character string.
| Data Type Name | Data Type Description |
|---|---|
| character varying(n), varchar(n) | It has the variable-length with limit |
| character(n), char(n) | It has the fixed-length and blank padded |
| text | It has the variable unlimited length |
Binary Data Types
The PostgreSQL Binary bytea Data Types stores the binary string.
| Data Type Name | Data Type Description |
|---|---|
| bytea | It stores 1 or 4 bytes along with actual and variable binary strings. |
Date/Time Data Types
The PostgreSQL Date/Time Data Types stores the full set of date and time values. The Date is considered based on the Gregorian calendar.
| Data Type Name | Data Type Storage Size | Data Type Description | Data Type Low Value | Data Type High Value |
|---|---|---|---|---|
| timestamp [ (p) ] [ without time zone ] | 8 bytes | It can store both date and time (no time zone) | 4713 BC | 294276 AD |
| timestamp [ (p) ] with time zone | 8 bytes | It can store both date and time, with time zone | 4713 BC | 294276 AD |
| date | 4 bytes | It stores date (no time of day) | 4713 BC | 5874897 AD |
| time [ (p) ] [ without time zone ] | 8 bytes | It stores time of day (no date) | 00:00:00 | 24:00:00 |
| time [ (p) ] with time zone | 12 bytes | It stores the time of day (no date), with time zone | 00:00:00+1559 | 24:00:00-1559 |
| interval [ fields ] [ (p) ] | 16 bytes | It stores the time interval | -178000000 years | 178000000 years |
Boolean Data Types
The PostgreSQL Boolean Data Types store the True and false values. The Third state of Boolean Data Type is unknown that is represented as a Null value.
| Data Type Name | Data Type Storage Size | Data Type Description |
|---|---|---|
| boolean | 1 byte | The status will be True or False. |
Enumerated Data Types
The PostgreSQL Enumerated Data Types stores the static ordered set of values and is similar to enum types of other programming languages such as days of the week.
Enumerated Data Types are created using the CREATE TYPE command as mentioned in the below example.
postgres=# CREATE TYPE weekname AS ENUM ('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday');
Geometric Data Types
The PostgreSQL Geometric Data Types is the representation of two-dimensional spatial objects. The basic types are Points, Lines, Boxes, Paths, Circles, and Polygons.
| Data Type Name | Data Type Storage Size | Data Type Representation | Data Type Description |
|---|---|---|---|
| point | 16 bytes | It represents the Point on a plane | (a,b) |
| line | 32 bytes | It represents the Infinite line (not fully implemented) | ((a1,b1),(a2,b2)) |
| lseg | 32 bytes | It represents the Finite line segment | ((a1,b1),(a2,b2)) |
| box | 32 bytes | It represents the Rectangular box | ((a1,b1),(a2,b2)) |
| path | 16+16n bytes | It represents the Closed path (similar to polygon) | ((a1,b1),...) |
| path | 16+16n bytes | It represents the Open path | [(a1,b1),...] |
| polygon | 40+16n | It represents the Polygon (similar to closed path) | ((a1,b1),...) |
| circle | 24 bytes | It represents the Circle | <(a,b),r> (center point and radius) |
Network Address Data Types
The PostgreSQL Network Address Data Types stores the information about the IPv4, IPv6, and MAC addresses. This data type is better if we compare it with the plain text type because it provides input error checking, functions, and specialized operators.
| Data Type Name | Data Type Storage Size | Data Type Description |
|---|---|---|
| cidr | 7 or 19 bytes | IPv4 and IPv6 networks |
| inet | 7 or 19 bytes | IPv4 and IPv6 hosts and networks |
| macaddr | 6 bytes | MAC addresses |
| macaddr8 | 8 bytes | MAC addresses (EUI-64 format) |
Bit String Data Types
The PostgreSQL Bit String Data Types stores the bit masks values that contain two values 1 or 0. It has two-bit types bit(n) and bit varying(n) where n represents the positive integer.
Text Search Data Types
The PostgreSQL Text Search Data Types provides two data types tsvector and tsquery to perform text search operations through the natural-language documents. The tsquery represents the query and the tsvector represents the optimized documents for text searching.
UUID Data Types
The PostgreSQL UUID Data Types also refers to the Universally Unique Identifiers are defined by the RFC 4122, ISO/IEC 9834-8:2005 and a sequence of lower-case hexadecimal digits that are separated by hyphens and a group of 8 digits and same is followed by three groups of 4 digits again followed by a group of 12 digits, so the for a total of 32 digits representing the 128 bits.
The example of UUID is b0ebc99-9c0b-4ef7-bc7d-7bb9bd370a21.
XML Data Types
The PostgreSQL XML Data Types are used to store the XML data. The advantage of storing XML data over text files is that input values are checked for well-formedness and there are support functions to perform the type-safe operation.
JSON Data Types
The PostgreSQL JSON Data Types also refers as the JavaScript Object Notation are used to store the JSON data types. We can store such data in text format as well but using JSON data type provides the advantage of checking whether each value is valid or not. The PostgreSQL supported functions are used to handle the JSON Data.
The example of PostgreSQL JSON Data Types is as below.
| JSON Data Type Example | JSON Data Type Output |
|---|---|
| to_json('Mohan said "Hello."'::text) | "Mohan said \"Hello.\"" |
Arrays Data Types
The example of PostgreSQL Arrays Data Types is used to define a column in a variable-length multidimensional array. We can create an Array of the user-defined base types, composite types, and range types.
In the following section, we will create a table named Emp_salary and define the quarterly_pay and Item columns as an Array. An Array is defined using the square brackets[]. Once the table is created we will insert a couple of records and fetch the data to see array data.
Command:
postgres=# CREATE TABLE Emp_salary (name text,quarterly_pay integer[],Item text[][]);
postgres=# INSERT INTO Emp_salary VALUES ('Mohan','{20000, 30000, 40000, 50000}','{{"Dinner", "meeting"}, {"training", "Lunch"}}');
postgres=# INSERT INTO Emp_salary VALUES ('Ram','{50000, 22000, 35000, 65000}','{{"Lunch", "consulting"}, {"meeting", "Dinner"}}');
postgres=# SELECT * FROM Emp_salary;
Output:
Composite Data Types
The PostgreSQL Composite Data Types stores the list of fields name and the data types of fields.
In the following example, we will declare a Composite Data Type named inve_item using the CREATE TYPE command and use that in another table named inv_data.
Command:
postgres=# CREATE TYPE inve_item AS (name text,supplier_id integer,price numeric);
postgres=# CREATE TABLE inv_data (item_details inve_item,count integer);
postgres=# INSERT INTO inv_data VALUES (ROW('suger', 100, 150), 200);
postgres=# SELECT * from inv_data;
Output:
Range Data Types
The PostgreSQL Range Data Types are used to store the range of data such as integer data starting from 1 to 10.
The following are the build-in range functions that are available.
| Range Data Type | JData Type Description |
|---|---|
| int4range | Range of integer |
| int8range | Range of bigint |
| numrange | Range of numeric |
| tsrange | Range of timestamp without time zone |
| tstzrange | Range of timestamp with time zone |
| daterange | Range of date |
Domain Data Types
The PostgreSQL Domain Data Types is the user-defined data type that works as constraints so that valid values can be inserted.
In the following example, we will create a Domain Data Type named posint_domain using the CREATE DOMAIN command and use it in a table named my_table.
Command:
postgres=# CREATE DOMAIN posint_domain AS integer CHECK (VALUE > 0);
postgres=# CREATE TABLE my_table (id_name posint_domain);
postgres=# INSERT INTO my_table VALUES(1); This command will work.
postgres=# CREATE DOMAIN posint_domain AS integer CHECK (VALUE > 0);This command will fail.
Output:
Object Identifier Data Types
The PostgreSQL Object Identifier Data Types is used to store the primary keys of system tables. It is basically used by PostgreSQL internally.
| Data Type Name | References | Data Type Description | Example |
|---|---|---|---|
| oid | any | It is the numeric object identifier | 564182 |
| regproc | pg_proc | It is the function name | sum |
| regprocedure | pg_proc | It is the function with argument types | sum(int4) |
| regoper | pg_operator | It is the operator name | + |
| regoperator | pg_operator | It is the operator with argument types | *(integer,integer) or -(NONE,integer) |
| regclass | pg_class | It is the relation name | pg_type |
| regtype | pg_type | It is the data type name | integer |
| regconfig | pg_ts_config | It is the text search configuration | English |
| regdictionary | pg_ts_dict | It is the text search dictionary | simple |
pg_lsn Data Types
The PostgreSQL pg_lsn Data Types stores the log sequence number also called LSN that indicates the location of the WAL file. It is an internal system data type of PostgreSQL.
Pseudo Data Types
The PostgreSQL Pseudo Data Types is used to declare the function argument or the result type. It can't be used as a column data type.
| Name | Description |
|---|---|
| any | Using this a function can accept any input data type. |
| anyarray | Using this a function can accept any array data type. |
| anyelement | Using this a function can accept any data type. |
| anyenum | Using this a function can accept any enum data type. |
| anynonarray | Using this a function can accept any non-array data type. |
| anyrange | Using this a function can accept any range data type. |
| cstring | Using this a function can accept or returns a null-terminated C string. |
| fdw_handler | This indicates that a foreign-data wrapper handler is declared to return fdw_handler. |
| internal | Using this a function can accept or returns a server-internal data type. |
| language_handler | Using this a procedural language call handler is declared to return language_handler. |
| record | It identifies a function returning an unspecified row type. |
| trigger | If declared as triggers then it will return trigger. |
| void | It is the indication that the function will not return any value. |