PostgreSQL Native Data Types

PostgreSQL offers a diverse set of native data types that users can leverage for various purposes. Users also have the flexibility to introduce new types using the CREATE TYPE command. The following is an overview of some key data types:

Numeric Types

Name Storage Size Description Range
SMALLINT 2 bytes Small-range integer -32768 to +32767
INTEGER 4 bytes Typical choice for integer -2147483648 to +2147483647
BIGINT 8 bytes Large-range integer -9223372036854775808 to +9223372036854775807
DECIMAL Variable User-specified precision, exact Up to 131072 digits before the decimal point; up to 16383 digits after the decimal point
NUMERIC Variable User-specified precision, exact Up to 131072 digits before the decimal point; up to 16383 digits after the decimal point
REAL 4 bytes Variable-precision, inexact 6 decimal digits precision
DOUBLE PRECISION 8 bytes Variable-precision, inexact 15 decimal digits precision
smallserial 2 bytes Small auto-incrementing integer 1 to 32767
serial 4 bytes Auto-incrementing integer 1 to 2147483647
BIGSERIAL 8 bytes Large auto-incrementing integer 1 to 9223372036854775807
int4range Range of integer
int8range Range of bigint
numrange Range of numeric

Date/Time Types

Name Storage Size Description Low Value High Value Resolution
TIMESTAMP (without TZ) 8 bytes Both date and time (no time zone) 4713 BC 294276 AD 1 microsecond / 14 digits
TIMESTAMP (with TZ) 8 bytes Both date and time, with time zone 4713 BC 294276 AD 1 microsecond / 14 digits
DATE 4 bytes Date (no time of day) 4713 BC 5874897 AD 1 day
TIME (without TZ) 8 bytes Time of day (no date) 00:00:00 24:00:00 1 microsecond / 14 digits
TIME (with TZ) 12 bytes Time of day only, with time zone 00:00:00+1459 24:00:00-1459 1 microsecond / 14 digits
INTERVAL 16 bytes Time interval -178000000 years 178000000 years 1 microsecond / 14 digits
tsrange Range of timestamp without TZ
tstzrange Range of timestamp with TZ
daterange Range of date

For more detailed information and additional data types, you can refer to the PostgreSQL Data Types Documentation.

Geometric Types

Name Storage Size Description Representation
point 16 bytes Point on a plane (x,y) (x, y)
line 32 bytes Infinite line {A,B,C} {A, B, C}
lseg 32 bytes Finite line segment ((x1,y1),(x2,y2)) ((x1, y1), (x2, y2))
BOX 32 bytes Rectangular box ((x1,y1),(x2,y2)) ((x1, y1), (x2, y2))
path 16+16n bytes Closed path (similar to polygon) ((x1, y1), ...)
path 16+16n bytes Open path [(x1, y1), ...] [(x1, y1), ...]
polygon 40+16n bytes Polygon (similar to closed path) ((x1, y1), ...)
CIRCLE 24 bytes Circle <(x, y), r> (center point and radius) <(x, y), r>

Network Address Types

Name Storage Size Description
CIDR 7 or 19 bytes IPv4 and IPv6 networks
INET 7 or 19 bytes IPv4 and IPv6 hosts and networks
macaddr 6 bytes MAC addresses

Character Types

Name Description
CHARACTER varying(n) Variable-length with limit
varchar(n) Variable-length with limit
character(n) Fixed-length, blank-padded
char(n) Fixed-length, blank-padded
TEXT Variable unlimited length

Arrays

In PostgreSQL, arrays can be created for any built-in, user-defined, or enum type. There is no default limit to an array, but you can specify it.

Declaring an Array:

SELECT INTEGER[];
SELECT INTEGER[3];
SELECT INTEGER[][];
SELECT INTEGER[3][3];
SELECT INTEGER ARRAY;
SELECT INTEGER ARRAY[3];

Creating an Array:

SELECT '{0,1,2}';
SELECT '{{0,1},{1,2}}';
SELECT ARRAY[0,1,2];
SELECT ARRAY[ARRAY[0,1],ARRAY[1,2]];

Accessing an Array: By default, PostgreSQL uses a one-based numbering convention for arrays. An array of n elements starts with ARRAY[1] and ends with ARRAY[n].