Defines a new data type for use in the database.
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 ] )
typename
The name of the new type being created, which may be up to 30 characters in length. All type names must be unique within a database, and may not begin with an underscore (which is reserved for implicit array types).
internallength
The internal length of the new type, in bytes.
externallength
The optional external (displayed) length of the new type.
input_function
The name of the new type’s input function. You must have already defined the function
using CREATE FUNCTION
, and it must act to convert data of the type’s
external form into the type’s internal form.
output_function
The name of the new type’s output function. This function must convert data of the type’s internal form into its displayable form.
element
The data type of individual array elements which this type addresses, if you intend
to create an array type manually. The element
must
be fixed-length data type.
delimiter
The value delimiter for the implicitly created array associated with the new type
(typename
[]
).
default
The default value for the new data type. If you do not specify a default, the default
value for an unspecified column will fall back to either a table-level DEFAULT
constraint or NULL
.
send_function
The name of the new type’s send
function. This function would
convert data of the type into a form that can be transferred to another machine, but is
not used by PostgreSQL as of 7.1.x, and can be omitted.
receive_function
The name of the new type’s receive
function. This function would
accept data of the form returned by send_function
,
and convert that into the type’s internal form, but it is also not used by PostgreSQL as
of 7.1.x, and can be omitted.
PASSEDBYVALUE
The optional PASSEDBYVALUE
keyword indicates that operators and
functions that use this data type should be passed the argument by value, rather than by
reference (the default). You may not use this option on types whose internal
representation is more than four bytes in length.
alignment
The storage alignment that this type will require. This must be either char
, int2
, int4
, or double
. If unspecified, int4
will be chosen by
default.
storage
The storage technique that will be used for the type. Set this to one of plain
, external
, extended
, or main
. If left unspecified, the storage type will default to plain
.
Use the CREATE TYPE
command to register a new, user-defined data type
within the current database. The PostgreSQL user that issues the command becomes the owner of
the data type.
For a type to be created, it must use two user-defined functions (written in C). These functions are the input and output functions of the data type. The input function converts the type’s external representation into an internal representation that can be used by the system objects associated with the type. The output function converts the internal representation back to an external representation.
Both the input and output functions must take a single argument of the opaque
type. The output function must return a value of type opaque
, while the input function should return a value of the type you intend to
create. Notice that this is done before the type is actually created.
You can set the type as either fixed or variable length. If you intend to create a
fixed-length type, set internallength
to set its
numeric length, in bytes. If you intend to create a variable-length type, use the VARIABLE
keyword instead of the internallength
parameter, and the length will be handled in the same
way as for the text
data type. Specify the external length in the same way,
using either a numeric value for externallength
, or
the VARIABLE
keyword.
When a new type is created, PostgreSQL automatically adds an implicit array type for the
new data type. Internally, this implicit array type is named _typename
(with a leading underscore). Any reference to a data type
called typename
[]
will
automatically be translated to the internal array type (_typename
).
If you wish to provide a delimiter character for the array type, use delimiter
to do so. This is the character used to separate array
elements within array constants passed to PostgreSQL (e.g., {1,2,3}
). This
is also the character used to separate elements in the external display of values for this
array type. By default the delimiter is set to a comma.
If you choose to manually create an array data type, you may provide the PostgreSQL
array_in
and array_out
functions as the input and
output function, respectively. You may then use the ELEMENT
keyword to
specify the data type of the array elements.
To define a system-wide default value for insertion on a column of the new data type
(which would ordinarily default to NULL
, in instances where a value is
neither provided by a user, or by a DEFAULT
constraint), use the DEFAULT
keyword. Note that, as of PostgreSQL 7.1.x, this must be the
internal representation of the default value.
The alignment
value dictates the internal storage
alignment of the new data type. Data types created with a variable internal length must be
either int4
or double
.
The storage
value determines the internal storage
method. Data types with fixed internal length can only be set to plain
.
Data types with variable internal length can be set to plain
, extended
, external
, or main
.
The plain
method causes data to be stored in an uncompressed, literal
representation. This representation is subject to a maximum length of 8 kilobytes. The
extended
method allows values that go over this limit to be compressed, as
well as to be stored outside of the physical location of the table if the size of the value
goes over the physical limit through PostgreSQL’s TOAST extension
(The Oversized Attribute Storage Technique, coined by Tom Lane).
The external
method is similar to the extended
method, but does not attempt to compress the value before using TOAST to store values over the
physical limit of the table. The main
method is also similar to the
extended
method, in that it supports compression and TOAST, but it prefers
to be maintained physically within the main table unless there is no other storage
alternative.
The following example demonstrates the creation of a new data type called zero
, which is a numeric data type always set to 0. First, the input and output
functions are created. Then, the type itself is created, referencing those functions, as
follows.
booktown=# CREATE FUNCTION zero_out(opaque) RETURNS opaque booktown-# AS '/usr/local/pgsql/lib/zero.so' LANGUAGE 'C'; CREATE booktown=# CREATE FUNCTION zero_in(opaque) RETURNS zero booktown-# AS '/usr/local/pgsql/lib/zero.so' LANGUAGE 'C'; NOTICE: ProcedureCreate: type 'zero' is not yet defined CREATE booktown=# CREATE TYPE zero (internallength = 16, booktown(# input = zero_in, output = zero_out); CREATE
18.227.134.133