Data Types

Introduction

PostgreSQL has a rich set of native data types available to users. Users can add new types to PostgreSQL using the CREATE TYPE command.

https://www.postgresql.org/docs/9.6/static/datatype.html

Arrays

In PostgreSQL you can create Arrays of any built-in, user-defined or enum type. In default there is no 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, that is, an array of n elements starts with array[1] and ends with array[n].

--accesing a spefific element
WITH arr AS (SELECT ARRAY[0,1,2] int_arr) SELECT int_arr[1] FROM arr;

int_arr
---------
        0
(1 row)

--sclicing an array
WITH arr AS (SELECT ARRAY[0,1,2] int_arr) SELECT int_arr[1:2] FROM arr;

int_arr
---------
    {0,1}
(1 row)

Getting information about an array

--array dimensions (as text)
with arr as (select ARRAY[0,1,2] int_arr) select array_dims(int_arr) from arr;

array_dims
------------
       [1:3]
(1 row)

--length of an array dimension
 WITH arr AS (SELECT ARRAY[0,1,2] int_arr) SELECT array_length(int_arr,1) FROM arr;

 array_length
 --------------
              3
 (1 row)

--total number of elements across all dimensions
 WITH arr AS (SELECT ARRAY[0,1,2] int_arr) SELECT cardinality(int_arr) FROM arr;
 
 cardinality
 -------------
             3
 (1 row)

Array functions

will be added

Character Types

NameDescription
character varying(n), varchar(n)variable-length with limit
character(n), char(n)fixed-length, blank padded
textvariable unlimited length

Date/ Time Types

NameStorage SizeDescriptionLow ValueHigh ValueResolution
timestamp (without time zone)8 bytesboth date and time (no time zone)4713 BC294276 AD1 microsecond / 14 digits
timestamp (with time zone)8 bytesboth date and time, with time zone4713 BC294276 AD1 microsecond / 14 digits
date4 bytesdate (no time of day)4713 BC5874897 AD1 day
time (without time zone)8 bytestime of day (no date)00:00:0024:00:001 microsecond / 14 digits
time (with time zone)12 bytestimes of day only, with time zone00:00:00+145924:00:00-14591 microsecond / 14 digits
interval16 bytestime interval-178000000 years178000000 years1 microsecond / 14 digits
tsrangerange of timestamp without time zone
tstzrangerange of timestamp with time zone
daterangerange of date

Geometric Types

NameStorage SizeDescriptionRepresentation
point16 bytesPoint on a plane(x,y)
line32 bytesInfinite line{A,B,C}
lseg32 bytesFinite line segment((x1,y1),(x2,y2))
box32 bytesRectangular box((x1,y1),(x2,y2))
path16+16n bytesClosed path (similar to polygon)((x1,y1),...)
path16+16n bytesOpen path[(x1,y1),...]
polygon40+16n bytesPolygon (similar to closed path)((x1,y1),...)
circle24 bytesCircle<(x,y),r> (center point and radius)

Network Adress Types

NameStorage SizeDescription
cidr7 or 19 bytesIPv4 and IPv6 networks
inet7 or 19 bytesIPv4 and IPv6 hosts and networks
macaddr6 bytesMAC addresses

Numeric Types

NameStorage SizeDescriptionRange
smallint2 bytessmall-range integer-32768 to +32767
integer4 bytesypical choice for integer-2147483648 to +2147483647
bigint8 byteslarge-range integer-9223372036854775808 to +9223372036854775807
decimalvariableuser-specified precision, exactup to 131072 digits before the decimal point; up to 16383 digits after the decimal point
numericvariableuser-specified precision, exactup to 131072 digits before the decimal point; up to 16383 digits after the decimal point
real4 bytesvariable-precision, inexact6 decimal digits precision
double precision8 bytesvariable-precision, inexact15 decimal digits precision
smallserial2 bytessmall autoincrementing integer1 to 32767
serial4 bytesautoincrementing integer1 to 2147483647
bigserial8 byteslarge autoincrementing integer1 to 9223372036854775807
int4rangeRange of integer
int8rangeRange of bigint
numrangeRange of numeric