Standard Data Types edit

PostgreSQL supports the basic set of data types which are defined by the SQL standard and described in the wikibook SQL (but: CLOB is called TEXT and BLOB is called BYTEA) [1].

Character Types
Character (CHAR)
Character Varying (VARCHAR)
Character Large Object (TEXT/CLOB)
Binary Types
Binary (BINARY)
Binary Varying (VARBINARY)
Binary Large Object (BYTEA/BLOB)
Numeric Types
Exact Numeric Types (NUMERIC, DECIMAL, SMALLINT, INTEGER, BIGINT)
Approximate Numeric Types (FLOAT, REAL, DOUBLE PRECISION)
Datetime Types
(DATE, TIME, TIMESTAMP. With and without timezone.)
Interval Type
(INTERVAL)
Boolean
XML
JSON
JSON and JSONB stores data according to RFC 7159. The difference between the two data types is, that JSON internally uses a pure text representation whereas JSONB uses a decomposed binary format.

In addition to this basic types there are some more predefined types as well as a mechanism to define own, composite data types.

Extensions to the Standard edit

SERIAL
SERIAL generates a sequence of integer values, usually used as a primary key. However SERIAL is not a real data type. Under the hood, it uses the type INTEGER and accomplishes it with a sequence.
ENUM
It defines a static, ordered set of values, eg: colors, days of the week, ... .
MONETARY
It represents currency values with fixed fractional precision.
GEOMETRIC
POINT, LINE, LSEG, BOX, PATH, POLYGON, and CIRCLE are supported 'out of the box' (without the need to install the extension PostGIS).
Network Address Types
MACADDR, MACADDR8: They hold MAC addresses.
INET: It holds an IPv4 or IPv6 host address and optionally a netmask, eg: 123.45.67.89 or 192.168.0.1/24. INET accepts nonzero bits to the right of the netmask (on input).
CIDR: It holds an IPv4 or IPv6 network specification and optionally a netmask, eg: 123.45.67.89 or 192.168.0.0/24. CIDR does not accept nonzero bits to the right of the netmask (on input).
Bit Types
BIT(n) and BIT VARYING(n) stores '0's and '1's in the same way as CHAR stores character.
UUID
It stores a sequence of 128 bits according to RFC 4122. Its main usage is to hold unique values.
HSTORE
key/value pairs (via EXTENSION HSTORE).

Special Types edit

Arrays edit

According to SQL:2011 PostgreSQL supports arrays. Columns of a table can be defined as variable-length multidimensional arrays of the above-presented data types.

CREATE TABLE tbl_1 (
  id         SERIAL,
  -- A one dimensional array of integers. It's possible to use multidimensional arrays, eg: INTEGER[][]
  column_1   INTEGER[]
);

-- construct arrays with curly braces or with the ARRAY[] syntax
INSERT INTO tbl_1 VALUES (1, '{1, 2, 3, 4}');
INSERT INTO tbl_1 VALUES (2, ARRAY[5, 6, 7]);

-- specify certain elements of an array with the [] syntax
SELECT * FROM tbl_1 WHERE column_1[2] = 2;
 id | column_1 
----+-----------
  1 | {1,2,3,4}

Composite Types edit

You can create new types by arranging an ordered set of data types - like a struct or record in other programming languages. This new type can be used at all places where the predefined data types are applicable (columns, function parameters, ...).

-- create a composite type
CREATE TYPE person AS (
  first_name    CHAR(15),
  last_name     CHAR(15)
);

-- Use the composite type to define columns with the desired structure
CREATE TABLE tbl_2 (
  id         SERIAL,
  -- the composite type
  pers       person,
  -- an array of up to 5 hobbies
  hobbies    CHAR(10) [5]
);

-- construct values of the composite type with () syntax
INSERT INTO tbl_2 VALUES (1, '(John D., Walker)', '{Sports, Chess}');

SELECT * FROM tbl_2;
 id |                 pers                  |           hobbies          
----+---------------------------------------+-----------------------------
  1 | ("John D.        "," Walker        ") | {"Sports    ","Chess     "}

Range Types edit

Range types are data types representing a range of values, eg: an integer range or a date or timestamp range. This is similar to a BETWEEN constraint. But it offers the additional possibility to ensure, that values of different rows do not overlap; see the description of an EXCLUSION CONSTRAINT with a GIST index

References edit