PostgreSQL data types are divided into base types, composite types, domains, and pseudo-types.
Base types are those, like
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. Base types are further subdivided
into scalar and array types. For each scalar type, a corresponding
array type is automatically created that can hold variable-size
arrays of that scalar type.
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. The user can access the component fields from SQL queries. Refer to Section 8.16 for more information on composite types.
A domain is based on a particular base type and for many purposes is interchangeable with its base type. However, a domain can have constraints that restrict its valid values to a subset of what the underlying base type would allow.
Domains can be created using the SQL command CREATE DOMAIN. Their creation and use is not discussed in this chapter.
There are a few “pseudo-types” for special purposes. Pseudo-types cannot appear as columns of tables or attributes of composite 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)
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
anyrange and others
anyelement, the actual range
type in the
anyrange positions must be a
range whose subtype is the same type appearing in the
anynonarray is treated exactly the same as
anyelement, but adds the additional
constraint that the actual type must not be an array type.
anyenum is treated exactly the same as
anyelement, but adds the additional
constraint that the actual type must be an enum type.
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
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 subscripting as
subscript(anyarray, 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
anyenum will only accept arrays of enum types.
anyenum do not represent separate type variables;
they are the same type as
just with an additional constraint. For example, declaring a
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 37.4.5)
can be polymorphic: this is accomplished by declaring its last
anyarray. 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.