CREATE TYPE - Defines a new base data type
SYNOPSIS
CREATE TYPE typename ( INPUT = input_function, OUTPUT = output_function
, INTERNALLENGTH = { internallength | VARIABLE }
[ , EXTERNALLENGTH = { externallength | VARIABLE } ]
[ , DEFAULT = "default" ]
[ , ELEMENT = element ] [ , DELIMITER = delimiter ]
[ , SEND = send_function ] [ , RECEIVE = receive_function ]
[ , PASSEDBYVALUE ]
[ , ALIGNMENT = alignment ]
[ , STORAGE = storage ]
)
INPUTS
typename
The name of a type to be created.
internallength
A literal value, which specifies the internal
length of the new type.
externallength
A literal value, which specifies the external (dis
played) length of the new type.
input_function
The name of a function, created by CREATE FUNCTION,
which converts data from its external form to the
type's internal form.
output_function
The name of a function, created by CREATE FUNCTION,
which converts data from its internal form to a
form suitable for display.
element
The type being created is an array; this specifies
the type of the array elements.
delimiter
The delimiter character for the array elements.
default
The default value for the data type. Usually this
is omitted, so that the default is NULL.
send_function
The name of a function, created by CREATE FUNCTION,
which converts data of this type into a form suit
able for transmission to another machine.
The name of a function, created by CREATE FUNCTION,
which converts data of this type from a form suit
able for transmission from another machine to
internal form.
alignment
Storage alignment requirement of the data type. If
specified, must be 'int4' or 'double'; the default
is 'int4'.
storage
Storage technique for the data type. If specified,
must be 'plain', 'external', 'extended', or 'main';
the default is 'plain'.
OUTPUTS
CREATE Message returned if the type is successfully cre
ated.
DESCRIPTION
CREATE TYPE allows the user to register a new user data
type with Postgres for use in the current data base. The
user who defines a type becomes its owner. typename is
the name of the new type and must be unique within the
types defined for this database.
CREATE TYPE requires the registration of two functions
(using create function) before defining the type. The rep
resentation of a new base type is determined by
input_function, which converts the type's external repre
sentation to an internal representation usable by the
operators and functions defined for the type. Naturally,
output_function performs the reverse transformation. Both
the input and output functions must be declared to take
one or two arguments of type "opaque".
New base data types can be fixed length, in which case
internallength is a positive integer, or variable length,
in which case Postgres assumes that the new type has the
same format as the Postgres-supplied data type, "text".
To indicate that a type is variable length, set internal
length to VARIABLE. The external representation is simi
larly specified using the externallength keyword.
To indicate that a type is an array and to indicate that a
type has array elements, indicate the type of the array
element using the element keyword. For example, to define
an array of 4-byte integers ("int4"), specify
ELEMENT = int4
type, delimiter can be set to a specific character. The
default delimiter is the comma (",").
A default value is optionally available in case a user
wants some specific bit pattern to mean "data not pre
sent." Specify the default with the DEFAULT keyword.
[Comment: How does the user specify that bit pattern and
associate it with the fact that the data is not present>]
The optional arguments send_function and receive_function
are used when the application program requesting Postgres
services resides on a different machine. In this case, the
machine on which Postgres runs may use a format for the
data type different from that used on the remote machine.
In this case it is appropriate to convert data items to a
standard form when sending from the server to the client
and converting from the standard format to the machine
specific format when the server receives the data from the
client. If these functions are not specified, then it is
assumed that the internal format of the type is acceptable
on all relevant machine architectures. For example, single
characters do not have to be converted if passed from a
Sun-4 to a DECstation, but many other types do.
The optional flag, PASSEDBYVALUE, indicates that operators
and functions which use this data type should be passed an
argument by value rather than by reference. Note that you
may not pass by value types whose internal representation
is more than four bytes.
The storage keyword allows selection of storage strategies
for variable-length data types (only plain is allowed for
fixed-length types). plain disables TOAST for the data
type: it will always be stored in-line and not compressed.
extended gives full TOAST capability: the system will
first try to compress a long data value, and will move the
value out of the main table row if it's still too long.
external allows the value to be moved out of the main
table, but the system will not try to compress it. main
allows compression, but discourages moving the value out
of the main table. (Data items with this storage method
may still be moved out of the main table if there is no
other way to make a row fit, but they will be kept in the
main table preferentially over extended and external
items.)
For new base types, a user can define operators, functions
and aggregates using the appropriate facilities described
in this section.
ARRAY TYPES
Two generalized built-in functions, array_in and
array_out, exist for quick creation of variable-length
existing Postgres type.
EXAMPLES
This command creates the box data type and then uses the
type in a table definition:
CREATE TYPE box (INTERNALLENGTH = 8,
INPUT = my_procedure_1, OUTPUT = my_procedure_2);
CREATE TABLE myboxes (id INT4, description box);
This command creates a variable length array type with
integer elements:
CREATE TYPE int4array (INPUT = array_in, OUTPUT = array_out,
INTERNALLENGTH = VARIABLE, ELEMENT = int4);
CREATE TABLE myarrays (id int4, numbers int4array);
This command creates a large object type and uses it in a
table definition:
CREATE TYPE bigobj (INPUT = lo_filein, OUTPUT = lo_fileout,
INTERNALLENGTH = VARIABLE);
CREATE TABLE big_objs (id int4, obj bigobj);
NOTES
Type names cannot begin with the underscore character
("_") and can only be 31 characters long. This is because
Postgres silently creates an array type for each base type
with a name consisting of the base type's name prepended
with an underscore.
Refer to DROP TYPE to remove an existing type.
See also CREATE FUNCTION, CREATE OPERATOR and the chapter
on Large Objects in the PostgreSQL Programmer's Guide.
COMPATIBILITY
SQL3
CREATE TYPE is an SQL3 statement.
Man(1) output converted with
man2html