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

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 TypeSize (byte)Min valueMax value
smallint2-32768+32767
integer4-2147483648+2147483647
bigint8-9223372036854775808+9223372036854775807

Numeric and Decimal

Numeric and Decimal are of variable size as the precision is user specified.

Data TypeSize (byte)Range
numericvariableup to 131072 digits before the decimal point; up to 16383 digits after the decimal point
decimalvariableup 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 TypeSize (byte)Range
real46 decimal digits precision
double precision815 decimal digits precision

Serial

This helps us to create auto-increment columns in table.

Data TypeSize (byte)Range
smallserial21 to 32767
serial41 to 2147483647
bigserial81 to 9223372036854775807

Boolean Type

Boolean types helps us to store boolean values (true/false) in a column.

Data TypeSize (byte)Values
boolean1true, 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 TypeSize (byte)Range
money8-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 TypeDescription
character varying(n), varchar(n)fixed-length, blank padded
textvariable unlimited length

Binary Type

This data type helps us to store binary string in a column.

Data TypeSize (byte)Range
bytea1 or 4 bytes plus the actual binary stringvariable-length binary string

Date/Time Type

This data type helps us to store date time value in a column.

Data TypeSize (byte)Min valueMax valueDescriptionResolution
timestamp84713 BC294276 ADDate and time without timezone1 microsecond / 14 digits
timestamptz84713 BC294276 ADDate and time with timezone1 microsecond / 14 digits
date44713 BC5874897 ADDate without time of day1 day
time800:00:0024:00:00Time of the day without date1 microsecond / 14 digits
timetz1200:00:00+145924:00:00-1459Time with timezone of the day without date1 microsecond / 14 digits
interval16-178000000 years178000000 yearsTime interval1 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.