Data Types

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

Numeric Types

|Name|Storage Size|Description|Range |---|---|---|---|---|---|--- |smallint|2 bytes|small-range integer|-32768 to +32767 |integer|4 bytes|ypical 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 autoincrementing integer|1 to 32767 |serial|4 bytes|autoincrementing integer|1 to 2147483647 |bigserial|8 bytes|large autoincrementing 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 time zone)|8 bytes|both date and time (no time zone)|4713 BC|294276 AD|1 microsecond / 14 digits |timestamp (with time zone)|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 time zone)|8 bytes|time of day (no date)|00:00:00|24:00:00|1 microsecond / 14 digits |time (with time zone)|12 bytes|times 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 time zone||| |tstzrange||range of timestamp with time zone||| |daterange||range of date|||

Geometric Types

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

Network Adress 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), varchar(n)|variable-length with limit |character(n), char(n)|fixed-length, blank padded |text|variable unlimited length

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