PostgreSQL
In this tutorial we will learn about of the commonly used data types in PostgreSQL.
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 types helps us to store numbers. The following table lists the different numeric types.
These data types are used to store integer values like -2, 0, 10 etc.
Numeric and Decimal are of variable size as the precision is user specified.
In PostgreSQL, numeric and decimal are equivalent.
numeric
decimal
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.
precision
scale
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.
NUMERIC(5, 3)
If scale is omitted like NUMERIC(precision) then the column will be able to save 0 and positive values.
NUMERIC(precision)
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.
This helps in saving numbers with different decimal digit precision.
This helps us to create auto-increment columns in table.
Boolean types helps us to store boolean values (true/false) in a column.
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
b77d1192-83e5-45f2-b0d1-9ab154a9ec85
This data type helps us to store XML (Extensible Markup Language) in a column.
This data type helps us to store JSON (JavaScript Object Notation) in a column.
This data type helps us to store a currency amount with a fixed fractional precision.
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.
This data type helps to store string in a column.
This data type helps us to store binary string in a column.
This data type helps us to store date time value in a column.
time, timestamp, and interval accept an optional precision value p which specifies the number of fractional digits retained in the seconds field.
p
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.