1CREATE TYPE(7)           PostgreSQL 13.4 Documentation          CREATE TYPE(7)
2
3
4

NAME

6       CREATE_TYPE - define a new data type
7

SYNOPSIS

9       CREATE TYPE name AS
10           ( [ attribute_name data_type [ COLLATE collation ] [, ... ] ] )
11
12       CREATE TYPE name AS ENUM
13           ( [ 'label' [, ... ] ] )
14
15       CREATE TYPE name AS RANGE (
16           SUBTYPE = subtype
17           [ , SUBTYPE_OPCLASS = subtype_operator_class ]
18           [ , COLLATION = collation ]
19           [ , CANONICAL = canonical_function ]
20           [ , SUBTYPE_DIFF = subtype_diff_function ]
21       )
22
23       CREATE TYPE name (
24           INPUT = input_function,
25           OUTPUT = output_function
26           [ , RECEIVE = receive_function ]
27           [ , SEND = send_function ]
28           [ , TYPMOD_IN = type_modifier_input_function ]
29           [ , TYPMOD_OUT = type_modifier_output_function ]
30           [ , ANALYZE = analyze_function ]
31           [ , INTERNALLENGTH = { internallength | VARIABLE } ]
32           [ , PASSEDBYVALUE ]
33           [ , ALIGNMENT = alignment ]
34           [ , STORAGE = storage ]
35           [ , LIKE = like_type ]
36           [ , CATEGORY = category ]
37           [ , PREFERRED = preferred ]
38           [ , DEFAULT = default ]
39           [ , ELEMENT = element ]
40           [ , DELIMITER = delimiter ]
41           [ , COLLATABLE = collatable ]
42       )
43
44       CREATE TYPE name
45

DESCRIPTION

47       CREATE TYPE registers a new data type for use in the current database.
48       The user who defines a type becomes its owner.
49
50       If a schema name is given then the type is created in the specified
51       schema. Otherwise it is created in the current schema. The type name
52       must be distinct from the name of any existing type or domain in the
53       same schema. (Because tables have associated data types, the type name
54       must also be distinct from the name of any existing table in the same
55       schema.)
56
57       There are five forms of CREATE TYPE, as shown in the syntax synopsis
58       above. They respectively create a composite type, an enum type, a range
59       type, a base type, or a shell type. The first four of these are
60       discussed in turn below. A shell type is simply a placeholder for a
61       type to be defined later; it is created by issuing CREATE TYPE with no
62       parameters except for the type name. Shell types are needed as forward
63       references when creating range types and base types, as discussed in
64       those sections.
65
66   Composite Types
67       The first form of CREATE TYPE creates a composite type. The composite
68       type is specified by a list of attribute names and data types. An
69       attribute's collation can be specified too, if its data type is
70       collatable. A composite type is essentially the same as the row type of
71       a table, but using CREATE TYPE avoids the need to create an actual
72       table when all that is wanted is to define a type. A stand-alone
73       composite type is useful, for example, as the argument or return type
74       of a function.
75
76       To be able to create a composite type, you must have USAGE privilege on
77       all attribute types.
78
79   Enumerated Types
80       The second form of CREATE TYPE creates an enumerated (enum) type, as
81       described in Section 8.7. Enum types take a list of quoted labels, each
82       of which must be less than NAMEDATALEN bytes long (64 bytes in a
83       standard PostgreSQL build). (It is possible to create an enumerated
84       type with zero labels, but such a type cannot be used to hold values
85       before at least one label is added using ALTER TYPE (ALTER_TYPE(7)).)
86
87   Range Types
88       The third form of CREATE TYPE creates a new range type, as described in
89       Section 8.17.
90
91       The range type's subtype can be any type with an associated b-tree
92       operator class (to determine the ordering of values for the range
93       type). Normally the subtype's default b-tree operator class is used to
94       determine ordering; to use a non-default operator class, specify its
95       name with subtype_opclass. If the subtype is collatable, and you want
96       to use a non-default collation in the range's ordering, specify the
97       desired collation with the collation option.
98
99       The optional canonical function must take one argument of the range
100       type being defined, and return a value of the same type. This is used
101       to convert range values to a canonical form, when applicable. See
102       Section 8.17.8 for more information. Creating a canonical function is a
103       bit tricky, since it must be defined before the range type can be
104       declared. To do this, you must first create a shell type, which is a
105       placeholder type that has no properties except a name and an owner.
106       This is done by issuing the command CREATE TYPE name, with no
107       additional parameters. Then the function can be declared using the
108       shell type as argument and result, and finally the range type can be
109       declared using the same name. This automatically replaces the shell
110       type entry with a valid range type.
111
112       The optional subtype_diff function must take two values of the subtype
113       type as argument, and return a double precision value representing the
114       difference between the two given values. While this is optional,
115       providing it allows much greater efficiency of GiST indexes on columns
116       of the range type. See Section 8.17.8 for more information.
117
118   Base Types
119       The fourth form of CREATE TYPE creates a new base type (scalar type).
120       To create a new base type, you must be a superuser. (This restriction
121       is made because an erroneous type definition could confuse or even
122       crash the server.)
123
124       The parameters can appear in any order, not only that illustrated
125       above, and most are optional. You must register two or more functions
126       (using CREATE FUNCTION) before defining the type. The support functions
127       input_function and output_function are required, while the functions
128       receive_function, send_function, type_modifier_input_function,
129       type_modifier_output_function and analyze_function are optional.
130       Generally these functions have to be coded in C or another low-level
131       language.
132
133       The input_function converts the type's external textual representation
134       to the internal representation used by the operators and functions
135       defined for the type.  output_function performs the reverse
136       transformation. The input function can be declared as taking one
137       argument of type cstring, or as taking three arguments of types
138       cstring, oid, integer. The first argument is the input text as a C
139       string, the second argument is the type's own OID (except for array
140       types, which instead receive their element type's OID), and the third
141       is the typmod of the destination column, if known (-1 will be passed if
142       not). The input function must return a value of the data type itself.
143       Usually, an input function should be declared STRICT; if it is not, it
144       will be called with a NULL first parameter when reading a NULL input
145       value. The function must still return NULL in this case, unless it
146       raises an error. (This case is mainly meant to support domain input
147       functions, which might need to reject NULL inputs.) The output function
148       must be declared as taking one argument of the new data type. The
149       output function must return type cstring. Output functions are not
150       invoked for NULL values.
151
152       The optional receive_function converts the type's external binary
153       representation to the internal representation. If this function is not
154       supplied, the type cannot participate in binary input. The binary
155       representation should be chosen to be cheap to convert to internal
156       form, while being reasonably portable. (For example, the standard
157       integer data types use network byte order as the external binary
158       representation, while the internal representation is in the machine's
159       native byte order.) The receive function should perform adequate
160       checking to ensure that the value is valid. The receive function can be
161       declared as taking one argument of type internal, or as taking three
162       arguments of types internal, oid, integer. The first argument is a
163       pointer to a StringInfo buffer holding the received byte string; the
164       optional arguments are the same as for the text input function. The
165       receive function must return a value of the data type itself. Usually,
166       a receive function should be declared STRICT; if it is not, it will be
167       called with a NULL first parameter when reading a NULL input value. The
168       function must still return NULL in this case, unless it raises an
169       error. (This case is mainly meant to support domain receive functions,
170       which might need to reject NULL inputs.) Similarly, the optional
171       send_function converts from the internal representation to the external
172       binary representation. If this function is not supplied, the type
173       cannot participate in binary output. The send function must be declared
174       as taking one argument of the new data type. The send function must
175       return type bytea. Send functions are not invoked for NULL values.
176
177       You should at this point be wondering how the input and output
178       functions can be declared to have results or arguments of the new type,
179       when they have to be created before the new type can be created. The
180       answer is that the type should first be defined as a shell type, which
181       is a placeholder type that has no properties except a name and an
182       owner. This is done by issuing the command CREATE TYPE name, with no
183       additional parameters. Then the C I/O functions can be defined
184       referencing the shell type. Finally, CREATE TYPE with a full definition
185       replaces the shell entry with a complete, valid type definition, after
186       which the new type can be used normally.
187
188       The optional type_modifier_input_function and
189       type_modifier_output_function are needed if the type supports
190       modifiers, that is optional constraints attached to a type declaration,
191       such as char(5) or numeric(30,2).  PostgreSQL allows user-defined types
192       to take one or more simple constants or identifiers as modifiers.
193       However, this information must be capable of being packed into a single
194       non-negative integer value for storage in the system catalogs. The
195       type_modifier_input_function is passed the declared modifier(s) in the
196       form of a cstring array. It must check the values for validity
197       (throwing an error if they are wrong), and if they are correct, return
198       a single non-negative integer value that will be stored as the column
199       “typmod”. Type modifiers will be rejected if the type does not have a
200       type_modifier_input_function. The type_modifier_output_function
201       converts the internal integer typmod value back to the correct form for
202       user display. It must return a cstring value that is the exact string
203       to append to the type name; for example numeric's function might return
204       (30,2). It is allowed to omit the type_modifier_output_function, in
205       which case the default display format is just the stored typmod integer
206       value enclosed in parentheses.
207
208       The optional analyze_function performs type-specific statistics
209       collection for columns of the data type. By default, ANALYZE will
210       attempt to gather statistics using the type's “equals” and “less-than”
211       operators, if there is a default b-tree operator class for the type.
212       For non-scalar types this behavior is likely to be unsuitable, so it
213       can be overridden by specifying a custom analysis function. The
214       analysis function must be declared to take a single argument of type
215       internal, and return a boolean result. The detailed API for analysis
216       functions appears in src/include/commands/vacuum.h.
217
218       While the details of the new type's internal representation are only
219       known to the I/O functions and other functions you create to work with
220       the type, there are several properties of the internal representation
221       that must be declared to PostgreSQL. Foremost of these is
222       internallength. Base data types can be fixed-length, in which case
223       internallength is a positive integer, or variable-length, indicated by
224       setting internallength to VARIABLE. (Internally, this is represented by
225       setting typlen to -1.) The internal representation of all
226       variable-length types must start with a 4-byte integer giving the total
227       length of this value of the type. (Note that the length field is often
228       encoded, as described in Section 69.2; it's unwise to access it
229       directly.)
230
231       The optional flag PASSEDBYVALUE indicates that values of this data type
232       are passed by value, rather than by reference. Types passed by value
233       must be fixed-length, and their internal representation cannot be
234       larger than the size of the Datum type (4 bytes on some machines, 8
235       bytes on others).
236
237       The alignment parameter specifies the storage alignment required for
238       the data type. The allowed values equate to alignment on 1, 2, 4, or 8
239       byte boundaries. Note that variable-length types must have an alignment
240       of at least 4, since they necessarily contain an int4 as their first
241       component.
242
243       The storage parameter allows selection of storage strategies for
244       variable-length data types. (Only plain is allowed for fixed-length
245       types.)  plain specifies that data of the type will always be stored
246       in-line and not compressed.  extended specifies that the system will
247       first try to compress a long data value, and will move the value out of
248       the main table row if it's still too long.  external allows the value
249       to be moved out of the main table, but the system will not try to
250       compress it.  main allows compression, but discourages moving the value
251       out of the main table. (Data items with this storage strategy might
252       still be moved out of the main table if there is no other way to make a
253       row fit, but they will be kept in the main table preferentially over
254       extended and external items.)
255
256       All storage values other than plain imply that the functions of the
257       data type can handle values that have been toasted, as described in
258       Section 69.2 and Section 37.13.1. The specific other value given merely
259       determines the default TOAST storage strategy for columns of a
260       toastable data type; users can pick other strategies for individual
261       columns using ALTER TABLE SET STORAGE.
262
263       The like_type parameter provides an alternative method for specifying
264       the basic representation properties of a data type: copy them from some
265       existing type. The values of internallength, passedbyvalue, alignment,
266       and storage are copied from the named type. (It is possible, though
267       usually undesirable, to override some of these values by specifying
268       them along with the LIKE clause.) Specifying representation this way is
269       especially useful when the low-level implementation of the new type
270       “piggybacks” on an existing type in some fashion.
271
272       The category and preferred parameters can be used to help control which
273       implicit cast will be applied in ambiguous situations. Each data type
274       belongs to a category named by a single ASCII character, and each type
275       is either “preferred” or not within its category. The parser will
276       prefer casting to preferred types (but only from other types within the
277       same category) when this rule is helpful in resolving overloaded
278       functions or operators. For more details see Chapter 10. For types that
279       have no implicit casts to or from any other types, it is sufficient to
280       leave these settings at the defaults. However, for a group of related
281       types that have implicit casts, it is often helpful to mark them all as
282       belonging to a category and select one or two of the “most general”
283       types as being preferred within the category. The category parameter is
284       especially useful when adding a user-defined type to an existing
285       built-in category, such as the numeric or string types. However, it is
286       also possible to create new entirely-user-defined type categories.
287       Select any ASCII character other than an upper-case letter to name such
288       a category.
289
290       A default value can be specified, in case a user wants columns of the
291       data type to default to something other than the null value. Specify
292       the default with the DEFAULT key word. (Such a default can be
293       overridden by an explicit DEFAULT clause attached to a particular
294       column.)
295
296       To indicate that a type is an array, specify the type of the array
297       elements using the ELEMENT key word. For example, to define an array of
298       4-byte integers (int4), specify ELEMENT = int4. More details about
299       array types appear below.
300
301       To indicate the delimiter to be used between values in the external
302       representation of arrays of this type, delimiter can be set to a
303       specific character. The default delimiter is the comma (,). Note that
304       the delimiter is associated with the array element type, not the array
305       type itself.
306
307       If the optional Boolean parameter collatable is true, column
308       definitions and expressions of the type may carry collation information
309       through use of the COLLATE clause. It is up to the implementations of
310       the functions operating on the type to actually make use of the
311       collation information; this does not happen automatically merely by
312       marking the type collatable.
313
314   Array Types
315       Whenever a user-defined type is created, PostgreSQL automatically
316       creates an associated array type, whose name consists of the element
317       type's name prepended with an underscore, and truncated if necessary to
318       keep it less than NAMEDATALEN bytes long. (If the name so generated
319       collides with an existing type name, the process is repeated until a
320       non-colliding name is found.) This implicitly-created array type is
321       variable length and uses the built-in input and output functions
322       array_in and array_out. The array type tracks any changes in its
323       element type's owner or schema, and is dropped if the element type is.
324
325       You might reasonably ask why there is an ELEMENT option, if the system
326       makes the correct array type automatically. The only case where it's
327       useful to use ELEMENT is when you are making a fixed-length type that
328       happens to be internally an array of a number of identical things, and
329       you want to allow these things to be accessed directly by subscripting,
330       in addition to whatever operations you plan to provide for the type as
331       a whole. For example, type point is represented as just two
332       floating-point numbers, which can be accessed using point[0] and
333       point[1]. Note that this facility only works for fixed-length types
334       whose internal form is exactly a sequence of identical fixed-length
335       fields. A subscriptable variable-length type must have the generalized
336       internal representation used by array_in and array_out. For historical
337       reasons (i.e., this is clearly wrong but it's far too late to change
338       it), subscripting of fixed-length array types starts from zero, rather
339       than from one as for variable-length arrays.
340

PARAMETERS

342       name
343           The name (optionally schema-qualified) of a type to be created.
344
345       attribute_name
346           The name of an attribute (column) for the composite type.
347
348       data_type
349           The name of an existing data type to become a column of the
350           composite type.
351
352       collation
353           The name of an existing collation to be associated with a column of
354           a composite type, or with a range type.
355
356       label
357           A string literal representing the textual label associated with one
358           value of an enum type.
359
360       subtype
361           The name of the element type that the range type will represent
362           ranges of.
363
364       subtype_operator_class
365           The name of a b-tree operator class for the subtype.
366
367       canonical_function
368           The name of the canonicalization function for the range type.
369
370       subtype_diff_function
371           The name of a difference function for the subtype.
372
373       input_function
374           The name of a function that converts data from the type's external
375           textual form to its internal form.
376
377       output_function
378           The name of a function that converts data from the type's internal
379           form to its external textual form.
380
381       receive_function
382           The name of a function that converts data from the type's external
383           binary form to its internal form.
384
385       send_function
386           The name of a function that converts data from the type's internal
387           form to its external binary form.
388
389       type_modifier_input_function
390           The name of a function that converts an array of modifier(s) for
391           the type into internal form.
392
393       type_modifier_output_function
394           The name of a function that converts the internal form of the
395           type's modifier(s) to external textual form.
396
397       analyze_function
398           The name of a function that performs statistical analysis for the
399           data type.
400
401       internallength
402           A numeric constant that specifies the length in bytes of the new
403           type's internal representation. The default assumption is that it
404           is variable-length.
405
406       alignment
407           The storage alignment requirement of the data type. If specified,
408           it must be char, int2, int4, or double; the default is int4.
409
410       storage
411           The storage strategy for the data type. If specified, must be
412           plain, external, extended, or main; the default is plain.
413
414       like_type
415           The name of an existing data type that the new type will have the
416           same representation as. The values of internallength,
417           passedbyvalue, alignment, and storage are copied from that type,
418           unless overridden by explicit specification elsewhere in this
419           CREATE TYPE command.
420
421       category
422           The category code (a single ASCII character) for this type. The
423           default is 'U' for “user-defined type”. Other standard category
424           codes can be found in Table 51.63. You may also choose other ASCII
425           characters in order to create custom categories.
426
427       preferred
428           True if this type is a preferred type within its type category,
429           else false. The default is false. Be very careful about creating a
430           new preferred type within an existing type category, as this could
431           cause surprising changes in behavior.
432
433       default
434           The default value for the data type. If this is omitted, the
435           default is null.
436
437       element
438           The type being created is an array; this specifies the type of the
439           array elements.
440
441       delimiter
442           The delimiter character to be used between values in arrays made of
443           this type.
444
445       collatable
446           True if this type's operations can use collation information. The
447           default is false.
448

NOTES

450       Because there are no restrictions on use of a data type once it's been
451       created, creating a base type or range type is tantamount to granting
452       public execute permission on the functions mentioned in the type
453       definition. This is usually not an issue for the sorts of functions
454       that are useful in a type definition. But you might want to think twice
455       before designing a type in a way that would require “secret”
456       information to be used while converting it to or from external form.
457
458       Before PostgreSQL version 8.3, the name of a generated array type was
459       always exactly the element type's name with one underscore character
460       (_) prepended. (Type names were therefore restricted in length to one
461       fewer character than other names.) While this is still usually the
462       case, the array type name may vary from this in case of maximum-length
463       names or collisions with user type names that begin with underscore.
464       Writing code that depends on this convention is therefore deprecated.
465       Instead, use pg_type.typarray to locate the array type associated with
466       a given type.
467
468       It may be advisable to avoid using type and table names that begin with
469       underscore. While the server will change generated array type names to
470       avoid collisions with user-given names, there is still risk of
471       confusion, particularly with old client software that may assume that
472       type names beginning with underscores always represent arrays.
473
474       Before PostgreSQL version 8.2, the shell-type creation syntax CREATE
475       TYPE name did not exist. The way to create a new base type was to
476       create its input function first. In this approach, PostgreSQL will
477       first see the name of the new data type as the return type of the input
478       function. The shell type is implicitly created in this situation, and
479       then it can be referenced in the definitions of the remaining I/O
480       functions. This approach still works, but is deprecated and might be
481       disallowed in some future release. Also, to avoid accidentally
482       cluttering the catalogs with shell types as a result of simple typos in
483       function definitions, a shell type will only be made this way when the
484       input function is written in C.
485

EXAMPLES

487       This example creates a composite type and uses it in a function
488       definition:
489
490           CREATE TYPE compfoo AS (f1 int, f2 text);
491
492           CREATE FUNCTION getfoo() RETURNS SETOF compfoo AS $$
493               SELECT fooid, fooname FROM foo
494           $$ LANGUAGE SQL;
495
496       This example creates an enumerated type and uses it in a table
497       definition:
498
499           CREATE TYPE bug_status AS ENUM ('new', 'open', 'closed');
500
501           CREATE TABLE bug (
502               id serial,
503               description text,
504               status bug_status
505           );
506
507       This example creates a range type:
508
509           CREATE TYPE float8_range AS RANGE (subtype = float8, subtype_diff = float8mi);
510
511       This example creates the base data type box and then uses the type in a
512       table definition:
513
514           CREATE TYPE box;
515
516           CREATE FUNCTION my_box_in_function(cstring) RETURNS box AS ... ;
517           CREATE FUNCTION my_box_out_function(box) RETURNS cstring AS ... ;
518
519           CREATE TYPE box (
520               INTERNALLENGTH = 16,
521               INPUT = my_box_in_function,
522               OUTPUT = my_box_out_function
523           );
524
525           CREATE TABLE myboxes (
526               id integer,
527               description box
528           );
529
530       If the internal structure of box were an array of four float4 elements,
531       we might instead use:
532
533           CREATE TYPE box (
534               INTERNALLENGTH = 16,
535               INPUT = my_box_in_function,
536               OUTPUT = my_box_out_function,
537               ELEMENT = float4
538           );
539
540       which would allow a box value's component numbers to be accessed by
541       subscripting. Otherwise the type behaves the same as before.
542
543       This example creates a large object type and uses it in a table
544       definition:
545
546           CREATE TYPE bigobj (
547               INPUT = lo_filein, OUTPUT = lo_fileout,
548               INTERNALLENGTH = VARIABLE
549           );
550           CREATE TABLE big_objs (
551               id integer,
552               obj bigobj
553           );
554
555       More examples, including suitable input and output functions, are in
556       Section 37.13.
557

COMPATIBILITY

559       The first form of the CREATE TYPE command, which creates a composite
560       type, conforms to the SQL standard. The other forms are PostgreSQL
561       extensions. The CREATE TYPE statement in the SQL standard also defines
562       other forms that are not implemented in PostgreSQL.
563
564       The ability to create a composite type with zero attributes is a
565       PostgreSQL-specific deviation from the standard (analogous to the same
566       case in CREATE TABLE).
567

SEE ALSO

569       ALTER TYPE (ALTER_TYPE(7)), CREATE DOMAIN (CREATE_DOMAIN(7)), CREATE
570       FUNCTION (CREATE_FUNCTION(7)), DROP TYPE (DROP_TYPE(7))
571
572
573
574PostgreSQL 13.4                      2021                       CREATE TYPE(7)
Impressum