PostgreSQL data types can be divided into base types, container types, domains, and pseudo-types.
Base types are those, like
integer, that are implemented below the level of
the SQL language (typically
in a low-level language such as C). They generally correspond
to what are often known as abstract data types. PostgreSQL can only operate on such types
through functions provided by the user and only understands the
behavior of such types to the extent that the user describes
them. The built-in base types are described in Chapter 8.
Enumerated (enum) types can be considered as a subcategory of base types. The main difference is that they can be created using just SQL commands, without any low-level programming. Refer to Section 8.7 for more information.
PostgreSQL has three kinds of “container” types, which are types that contain multiple values of other types. These are arrays, composites, and ranges.
Arrays can hold multiple values that are all of the same type. An array type is automatically created for each base type, composite type, range type, and domain type. But there are no arrays of arrays. So far as the type system is concerned, multi-dimensional arrays are the same as one-dimensional arrays. Refer to Section 8.15 for more information.
Composite types, or row types, are created whenever the user creates a table. It is also possible to use CREATE TYPE to define a “stand-alone” composite type with no associated table. A composite type is simply a list of types with associated field names. A value of a composite type is a row or record of field values. Refer to Section 8.16 for more information.
A range type can hold two values of the same type, which are the lower and upper bounds of the range. Range types are user-created, although a few built-in ones exist. Refer to Section 8.17 for more information.
A domain is based on a particular underlying type and for many purposes is interchangeable with its underlying type. However, a domain can have constraints that restrict its valid values to a subset of what the underlying type would allow. Domains are created using the SQL command CREATE DOMAIN. Refer to Section 8.18 for more information.
There are a few “pseudo-types” for special purposes. Pseudo-types cannot appear as columns of tables or components of container types, but they can be used to declare the argument and result types of functions. This provides a mechanism within the type system to identify special classes of functions. Table 8.25 lists the existing pseudo-types.
Five pseudo-types of special interest are
which are collectively called polymorphic
types. Any function declared using these types is said to
be a polymorphic function. A
polymorphic function can operate on many different data types,
with the specific data type(s) being determined by the data
types actually passed to it in a particular call.
Polymorphic arguments and results are tied to each other and
are resolved to a specific data type when a query calling a
polymorphic function is parsed. Each position (either argument
or return value) declared as
anyelement is allowed to have any specific actual
data type, but in any given call they must all be the
same actual type. Each
position declared as
have any array data type, but similarly they must all be the
same type. And similarly, positions declared as
anyrange must all be the same range type.
Furthermore, if there are positions declared
anyarray and others declared
anyelement, the actual array type in the
anyarray positions must be an array
whose elements are the same type appearing in the
anyelement positions. Similarly, if there are
actual range type in the
positions must be a range whose subtype is the same type
appearing in the
anynonarray is treated
exactly the same as
adds the additional constraint that the actual type must not be
an array type.
anyenum is treated
exactly the same as
adds the additional constraint that the actual type must be an
Thus, when more than one argument position is declared with
a polymorphic type, the net effect is that only certain
combinations of actual argument types are allowed. For example,
a function declared as
anyelement) will take any two input values, so long as
they are of the same data type.
When the return value of a function is declared as a
polymorphic type, there must be at least one argument position
that is also polymorphic, and the actual data type supplied as
the argument determines the actual result type for that call.
For example, if there were not already an array subscripting
mechanism, one could define a function that implements
integer) returns anyelement. This declaration constrains
the actual first argument to be an array type, and allows the
parser to infer the correct result type from the actual first
argument's type. Another example is that a function declared as
f(anyarray) returns anyenum will
only accept arrays of enum types.
anyenum do not represent separate
type variables; they are the same type as
anyelement, just with an additional constraint.
For example, declaring a function as
f(anyelement, anyenum) is equivalent to
declaring it as
anyenum): both actual arguments have to be the same enum
A variadic function (one taking a variable number of
arguments, as in
Section 38.5.5) can be polymorphic: this is
accomplished by declaring its last parameter as
For purposes of argument matching and determining the actual
result type, such a function behaves the same as if you had
written the appropriate number of
If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.