Contents / Previous / Next

Data Types

Online Help: \dT in psql shows all data types

PostgreSQL data types:

Category Type Description
Character string TEXT  variable storage length
  VARCHAR (length) variable storage length with maximum length
  CHAR (length) fixed storage length, blank-padded to length,
         internally BPCHAR
Number INTEGER  integer, \( \pm \)2 billion range, internally INT4
  INT2  integer, \( \pm \)32 thousand range
  INT8  integer, \( \pm 4\times 10^{18} \) range
  OID  object identifier
  NUMERIC (precision, decimal) number, user-defined precision and decimal location
  FLOAT  floating-point number, 15-digit precision,
         internally FLOAT8
  FLOAT4  floating-point number, 6-digit precision
Temporal DATE  date
  TIME  time
  TIMESTAMP  date and time
  INTERVAL  interval of time
Logical BOOLEAN  boolean, true or false
Geometric POINT  point
  LSEG  line segment
  PATH  list of points
  BOX  rectangle
  CIRCLE  circle
  POLYGON  polygon
Network INET  IP address with optional netmask
  CIDR  IP network address
  MACADDR  Ethernet MAC address

Except for the number types, all entered values must be surrounded by single quotes.

Character String

TEXT does not limit the number of characters stored.
VARCHAR(length) limits the length of the field to length characters.
Both TEXT and VARCHAR() store only the number of characters in the string.
CHAR(length) is similar to VARCHAR(),
except it always stores exactly length characters.
This type pads the value with trailing spaces to achieve the specified length,
and provides slightly faster access than TEXT or VARCHAR().

Temporal

DATE allows storage of a single date consisting of a year, month, and day.
The format used to input and display dates is controlled by
the DATESTYLE setting (with SET DATESTYLE TO style) :

  Output for
Style Optional Ordering February 1, 1983
ISO   1983-02-01
POSTGRES US or NONEUROPEAN 02-01-1983
POSTGRES EUROPEAN 01-02-1983
SQL US or NONEUROPEAN 02/01/1983
SQL EUROPEAN 01/02/1983
German 01.02.1983

TIME allows storage of an hour, minute, and second, separated by colons.
TIMESTAMP stores both the date and the time, for example, 2000-7-12 17:34:29.

Logical

The only logical type is BOOLEAN. A BOOLEAN field can store only true or false, NULL.
You can input true as true, t, yes, y, or 1. False can be input as false, f, no, n, or 0.

Geometric

The geometric types support storage of geometric primitives:

Type Example Description
POINT  (2,7) (x,y) coordinates
LSEG  [(0,0),(1,3)] start and stop points of a line segment
PATH  ((0,0),(3,0),(4,5),(1,6)) ( ) is a closed path, [ ] is an open path
Box  (1,1),(3,3) opposite corner points of a rectangle
CIRCLE  <(1,2),60> center point and radius
POLYGON  ((3,1),(3,3),(1,0)) points form closed polygon

Network

INET allows storage of an IP address, with or without a netmask.
CIDR stores IP network addresses.
MACADDR stores MAC (Media Access Control) addresses,
which are assigned to Ethernet network cards (something like: 0:50:4:1d:f6:db).

Null Values

SQL uses a special value, called the null value, to mean that a particular value isn't known.

Type Casts

In most cases, values of one type are converted to another type automatically.
You can explicitly cast from one data type to another with: CAST ( expression AS type ) or expression::type or typename ( expression ) Example: To convert a column date_col of type DATE to type TEXT, use CAST(date_col AS TEXT).

The syntax with :: is historical PostgreSQL usage (same example: date_col::text).

The third only works for types whose names are also valid as function names (for example not with double).

Arrays

PostgreSQL allows columns of a table to be defined as variable-length multidimensional arrays, example: CREATE TABLE sal_emp ( name text, pay_by_quarter integer[], schedule text[][] ); You may also specify the dimesions. Values that do not match the dimensions specified at the time of column creation are not rejected.


To INSERT values are enclosed within curly braces and separated by commas:

INSERT INTO sal_emp VALUES ('Carol', '{20000, 25000, 25000, 25000}', '{{"talk", "consult"}, {"meeting"}}');
The array subscript numbers are written within square brackets, starting with one (an array of n elements starts with array[1] and ends with array[n]), example: SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2];