PostgreSQL - Data Types
PostgreSQL
In this tutorial we will learn about of the commonly used data types in PostgreSQL.
About Data Types
Data types tells us about the type of data that is stored in a column.
Following are some of the commonly used data types in PostgreSQL.
- Numeric Type
- Boolean Type
- UUID Type
- XML Type
- JSON Type
- Monetary Type
- Character Type
- Binary Type
- Date/Time Type
Numeric Type
Numeric types helps us to store numbers. The following table lists the different numeric types.
Integer
These data types are used to store integer values like -2, 0, 10 etc.
| Data Type | Size (byte) | Min value | Max value |
|---|---|---|---|
| smallint | 2 | -32768 | +32767 |
| integer | 4 | -2147483648 | +2147483647 |
| bigint | 8 | -9223372036854775808 | +9223372036854775807 |
Numeric and Decimal
Numeric and Decimal are of variable size as the precision is user specified.
| Data Type | Size (byte) | Range |
|---|---|---|
| numeric | variable | up to 131072 digits before the decimal point; up to 16383 digits after the decimal point |
| decimal | variable | up to 131072 digits before the decimal point; up to 16383 digits after the decimal point |
In PostgreSQL, numeric and decimal are equivalent.
To create a column of type numeric we use the following syntax.
NUMERIC(precision, scale)
Where, precision represents the total number of digits and scale represents the number of digits in the fraction part.
Let's say we want to save the following number 12.345 then the column will be declared as NUMERIC(5, 3) because there are 5 digits in total and 3 are in the fraction.
If scale is omitted like NUMERIC(precision) then the column will be able to save 0 and positive values.
If both precision and scale is omitted then the column will be able to store up to 131072 digits before decimal point and up to 16383 digits after the decimal point.
Floating point
This helps in saving numbers with different decimal digit precision.
| Data Type | Size (byte) | Range |
|---|---|---|
| real | 4 | 6 decimal digits precision |
| double precision | 8 | 15 decimal digits precision |
Serial
This helps us to create auto-increment columns in table.
| Data Type | Size (byte) | Range |
|---|---|---|
| smallserial | 2 | 1 to 32767 |
| serial | 4 | 1 to 2147483647 |
| bigserial | 8 | 1 to 9223372036854775807 |
Boolean Type
Boolean types helps us to store boolean values (true/false) in a column.
| Data Type | Size (byte) | Values |
|---|---|---|
| boolean | 1 | true, false |
UUID Type
This data type helps us to store UUID (Universally Unique Identifiers) in a column.
UUID also called a Global Unique IDentifier (GUID), is a 128-bit value consisting of hyphen separated groups of hexadecimal digits.
Example UUID value: b77d1192-83e5-45f2-b0d1-9ab154a9ec85
XML Type
This data type helps us to store XML (Extensible Markup Language) in a column.
JSON Type
This data type helps us to store JSON (JavaScript Object Notation) in a column.
Monetary Type
This data type helps us to store a currency amount with a fixed fractional precision.
| Data Type | Size (byte) | Range |
|---|---|---|
| money | 8 | -92233720368547758.08 to +92233720368547758.07 |
The fractional precision is determined by the database's lc_monetary setting.
Values of the numeric, int, and bigint data types can be cast to money.
Floating point numbers should not be used to handle money due to the potential for rounding errors.
Character Type
This data type helps to store string in a column.
| Data Type | Description |
|---|---|
| character varying(n), varchar(n) | fixed-length, blank padded |
| text | variable unlimited length |
Binary Type
This data type helps us to store binary string in a column.
| Data Type | Size (byte) | Range |
|---|---|---|
| bytea | 1 or 4 bytes plus the actual binary string | variable-length binary string |
Date/Time Type
This data type helps us to store date time value in a column.
| Data Type | Size (byte) | Min value | Max value | Description | Resolution |
|---|---|---|---|---|---|
| timestamp | 8 | 4713 BC | 294276 AD | Date and time without timezone | 1 microsecond / 14 digits |
| timestamptz | 8 | 4713 BC | 294276 AD | Date and time with timezone | 1 microsecond / 14 digits |
| date | 4 | 4713 BC | 5874897 AD | Date without time of day | 1 day |
| time | 8 | 00:00:00 | 24:00:00 | Time of the day without date | 1 microsecond / 14 digits |
| timetz | 12 | 00:00:00+1459 | 24:00:00-1459 | Time with timezone of the day without date | 1 microsecond / 14 digits |
| interval | 16 | -178000000 years | 178000000 years | Time interval | 1 microsecond / 14 digits |
time, timestamp, and interval accept an optional precision value p which specifies the number of fractional digits retained in the seconds field.
By default, there is no explicit bound on precision. The allowed range of p is from 0 to 6 for the timestamp and interval types.
For the time types, the allowed range of p is from 0 to 6 when eight-byte integer storage is used, or from 0 to 10 when floating-point storage is used.
ADVERTISEMENT