PostgreSQL - Data Types - PostgreSQL - DYclassroom

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
Numeric and Decimal
Floating point
Serial

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.

PostgreSQL - Data Types