PostgreSQL - Data Types

PostgreSQL - Data Types

PostgreSQL

Share

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 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.

Share