37.2. The PostgreSQL Type System
PostgreSQL data types are divided into base types, composite types, domains, and pseudo-types.
37.2.1. Base Types
Base types are those, like
int4, 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. 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
37.2.2. Composite Types
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.
37.2.5. Polymorphic 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
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
anyarray can 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
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
Similarly, if there are positions declared
and others declared
anyelement, the actual range type in
anyrange positions must be a range whose subtype is
the same type appearing in the
anynonarray is treated exactly the same as
but adds the additional constraint that the actual type must not be
an array type.
anyenum is treated exactly the same as
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 declared as
equal(anyelement, 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
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
is equivalent to declaring it as
both actual arguments have to be the same enum type.
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 parameter as
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