PostgreSQL Data Types
A data type specifies what kind of data you want to store in the table field. While creating a table, for each column, you have to use a datatype. It identifies a particular type of data, like integer, Boolean, floating points, and so on.
In this section, we are going to discuss the various data types used in PostgreSQL.
In PostgreSQL, each database table has many columns and has precise data types for each column. It supports an extensive range of data types.
Also, users can create their custom data type using the CREATE TYPE SQL command. And these data types have the following advantages:
- Performance:It enhances our performance if we are using these data types correctly and efficiently to store the data values.
- Validation: The correct use of data types involves the validation of data and dismissal of data outside the scope of the data type.
- Compactness:It stores efficiently, as a column can store a single type of value.
- Consistency: The activities in contradiction of columns of the same data type provide reliable results and are usually the fastest.
In PostgreSQL, we have many sets of data types; let us understand them one by one:
- Numeric datatype
- Character datatype
- Date/time datatype
- Monetary data type
- Binary data type
- Boolean data type
- Enumerated data type
- Geometric data type
- Text search data type
- UUID data type
- Network address type
- JSON data type
- Bit string type
- XML data type
- Range data type
- Arrays
- Composite data type
- Object identifiers type
- Pseudo data type
- pg-Isn data type
Numeric Data Type
Numeric datatype is used to specify the numeric data into the table. It contains the following:
- four- and eight-byte floating-point numbers
- two-, four-, and eight-byte integers
- Selectable-precision decimals.
The below table contains all numeric data types that support in PostgreSQL:
| name | description | storage size | range |
|---|---|---|---|
| smallint | stores whole numbers, small range. | 2 bytes | -32768 to +32767 |
| integer | stores whole numbers.use this when you want to store typical integers. | 4 bytes | -2147483648 to +2147483647 |
| bigint | stores whole numbers, large range. | 8 bytes | -9223372036854775808 to 9223372036854775807 |
| decimal | user-specified precision, exact | variable | up to 131072 digits before the decimal point; up to 16383 digits after the decimal point. |
| numeric | user-specified precision, exact | variable | up to 131072 digits before the decimal point; up to 16383 digits after the decimal point. |
| real | variable-precision, inexact | 4 bytes | 6 decimal digits precision. |
| double precision | variable-precision, inexact | 8 bytes | 15 decimal digits precision |
| serial | auto incrementing integer | 4 bytes | 1 to 2147483647 |
| bigserial | large auto incrementing integer | 8 bytes | 1 to 9223372036854775807 |
Character Data Type
In PostgreSQL, we have various general purpose character data types, and these data types are used to represent the character type values.
The below table contains all Character data types that are supported in PostgreSQL:
| Datatype | Explanation |
|---|---|
| char(size) | Here size is the number of characters to store. Fixed-length strings. Space padded on right to equal size characters. |
| character(size) | Here size is the number of characters to store. Fixed-length strings. Space padded on right to equal size characters. |
| varchar(size) | Here size is the number of characters to store. Variable-length string. |
| character varying(size) | Here size is the number of characters to store. Variable-length string. |
| text | Variable-length string. |
Date/Time Data Type
The PostgreSQL supports the complete set of SQL date and times data types. The date/time datatypes are used to represent the columns using date and time values. Based on the Gregorian calendar, the dates are counted.
The below table contains all date/ time data types that are supported in PostgreSQL:
| Name |
|---|