1CREATE TYPE(7)           PostgreSQL 16.1 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           [ , MULTIRANGE_TYPE_NAME = multirange_type_name ]
22       )
23
24       CREATE TYPE name (
25           INPUT = input_function,
26           OUTPUT = output_function
27           [ , RECEIVE = receive_function ]
28           [ , SEND = send_function ]
29           [ , TYPMOD_IN = type_modifier_input_function ]
30           [ , TYPMOD_OUT = type_modifier_output_function ]
31           [ , ANALYZE = analyze_function ]
32           [ , SUBSCRIPT = subscript_function ]
33           [ , INTERNALLENGTH = { internallength | VARIABLE } ]
34           [ , PASSEDBYVALUE ]
35           [ , ALIGNMENT = alignment ]
36           [ , STORAGE = storage ]
37           [ , LIKE = like_type ]
38           [ , CATEGORY = category ]
39           [ , PREFERRED = preferred ]
40           [ , DEFAULT = default ]
41           [ , ELEMENT = element ]
42           [ , DELIMITER = delimiter ]
43           [ , COLLATABLE = collatable ]
44       )
45
46       CREATE TYPE name
47

DESCRIPTION

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

PARAMETERS

382       name
383           The name (optionally schema-qualified) of a type to be created.
384
385       attribute_name
386           The name of an attribute (column) for the composite type.
387
388       data_type
389           The name of an existing data type to become a column of the
390           composite type.
391
392       collation
393           The name of an existing collation to be associated with a column of
394           a composite type, or with a range type.
395
396       label
397           A string literal representing the textual label associated with one
398           value of an enum type.
399
400       subtype
401           The name of the element type that the range type will represent
402           ranges of.
403
404       subtype_operator_class
405           The name of a b-tree operator class for the subtype.
406
407       canonical_function
408           The name of the canonicalization function for the range type.
409
410       subtype_diff_function
411           The name of a difference function for the subtype.
412
413       multirange_type_name
414           The name of the corresponding multirange type.
415
416       input_function
417           The name of a function that converts data from the type's external
418           textual form to its internal form.
419
420       output_function
421           The name of a function that converts data from the type's internal
422           form to its external textual form.
423
424       receive_function
425           The name of a function that converts data from the type's external
426           binary form to its internal form.
427
428       send_function
429           The name of a function that converts data from the type's internal
430           form to its external binary form.
431
432       type_modifier_input_function
433           The name of a function that converts an array of modifier(s) for
434           the type into internal form.
435
436       type_modifier_output_function
437           The name of a function that converts the internal form of the
438           type's modifier(s) to external textual form.
439
440       analyze_function
441           The name of a function that performs statistical analysis for the
442           data type.
443
444       subscript_function
445           The name of a function that defines what subscripting a value of
446           the data type does.
447
448       internallength
449           A numeric constant that specifies the length in bytes of the new
450           type's internal representation. The default assumption is that it
451           is variable-length.
452
453       alignment
454           The storage alignment requirement of the data type. If specified,
455           it must be char, int2, int4, or double; the default is int4.
456
457       storage
458           The storage strategy for the data type. If specified, must be
459           plain, external, extended, or main; the default is plain.
460
461       like_type
462           The name of an existing data type that the new type will have the
463           same representation as. The values of internallength,
464           passedbyvalue, alignment, and storage are copied from that type,
465           unless overridden by explicit specification elsewhere in this
466           CREATE TYPE command.
467
468       category
469           The category code (a single ASCII character) for this type. The
470           default is 'U' for “user-defined type”. Other standard category
471           codes can be found in Table 53.65. You may also choose other ASCII
472           characters in order to create custom categories.
473
474       preferred
475           True if this type is a preferred type within its type category,
476           else false. The default is false. Be very careful about creating a
477           new preferred type within an existing type category, as this could
478           cause surprising changes in behavior.
479
480       default
481           The default value for the data type. If this is omitted, the
482           default is null.
483
484       element
485           The type being created is an array; this specifies the type of the
486           array elements.
487
488       delimiter
489           The delimiter character to be used between values in arrays made of
490           this type.
491
492       collatable
493           True if this type's operations can use collation information. The
494           default is false.
495

NOTES

497       Because there are no restrictions on use of a data type once it's been
498       created, creating a base type or range type is tantamount to granting
499       public execute permission on the functions mentioned in the type
500       definition. This is usually not an issue for the sorts of functions
501       that are useful in a type definition. But you might want to think twice
502       before designing a type in a way that would require “secret”
503       information to be used while converting it to or from external form.
504
505       Before PostgreSQL version 8.3, the name of a generated array type was
506       always exactly the element type's name with one underscore character
507       (_) prepended. (Type names were therefore restricted in length to one
508       fewer character than other names.) While this is still usually the
509       case, the array type name may vary from this in case of maximum-length
510       names or collisions with user type names that begin with underscore.
511       Writing code that depends on this convention is therefore deprecated.
512       Instead, use pg_type.typarray to locate the array type associated with
513       a given type.
514
515       It may be advisable to avoid using type and table names that begin with
516       underscore. While the server will change generated array type names to
517       avoid collisions with user-given names, there is still risk of
518       confusion, particularly with old client software that may assume that
519       type names beginning with underscores always represent arrays.
520
521       Before PostgreSQL version 8.2, the shell-type creation syntax CREATE
522       TYPE name did not exist. The way to create a new base type was to
523       create its input function first. In this approach, PostgreSQL will
524       first see the name of the new data type as the return type of the input
525       function. The shell type is implicitly created in this situation, and
526       then it can be referenced in the definitions of the remaining I/O
527       functions. This approach still works, but is deprecated and might be
528       disallowed in some future release. Also, to avoid accidentally
529       cluttering the catalogs with shell types as a result of simple typos in
530       function definitions, a shell type will only be made this way when the
531       input function is written in C.
532
533       In PostgreSQL version 16 and later, it is desirable for base types'
534       input functions to return “soft” errors using the new
535       errsave()/ereturn() mechanism, rather than throwing ereport()
536       exceptions as in previous versions. See src/backend/utils/fmgr/README
537       for more information.
538

EXAMPLES

540       This example creates a composite type and uses it in a function
541       definition:
542
543           CREATE TYPE compfoo AS (f1 int, f2 text);
544
545           CREATE FUNCTION getfoo() RETURNS SETOF compfoo AS $$
546               SELECT fooid, fooname FROM foo
547           $$ LANGUAGE SQL;
548
549       This example creates an enumerated type and uses it in a table
550       definition:
551
552           CREATE TYPE bug_status AS ENUM ('new', 'open', 'closed');
553
554           CREATE TABLE bug (
555               id serial,
556               description text,
557               status bug_status
558           );
559
560       This example creates a range type:
561
562           CREATE TYPE float8_range AS RANGE (subtype = float8, subtype_diff = float8mi);
563
564       This example creates the base data type box and then uses the type in a
565       table definition:
566
567           CREATE TYPE box;
568
569           CREATE FUNCTION my_box_in_function(cstring) RETURNS box AS ... ;
570           CREATE FUNCTION my_box_out_function(box) RETURNS cstring AS ... ;
571
572           CREATE TYPE box (
573               INTERNALLENGTH = 16,
574               INPUT = my_box_in_function,
575               OUTPUT = my_box_out_function
576           );
577
578           CREATE TABLE myboxes (
579               id integer,
580               description box
581           );
582
583       If the internal structure of box were an array of four float4 elements,
584       we might instead use:
585
586           CREATE TYPE box (
587               INTERNALLENGTH = 16,
588               INPUT = my_box_in_function,
589               OUTPUT = my_box_out_function,
590               ELEMENT = float4
591           );
592
593       which would allow a box value's component numbers to be accessed by
594       subscripting. Otherwise the type behaves the same as before.
595
596       This example creates a large object type and uses it in a table
597       definition:
598
599           CREATE TYPE bigobj (
600               INPUT = lo_filein, OUTPUT = lo_fileout,
601               INTERNALLENGTH = VARIABLE
602           );
603           CREATE TABLE big_objs (
604               id integer,
605               obj bigobj
606           );
607
608       More examples, including suitable input and output functions, are in
609       Section 38.13.
610

COMPATIBILITY

612       The first form of the CREATE TYPE command, which creates a composite
613       type, conforms to the SQL standard. The other forms are PostgreSQL
614       extensions. The CREATE TYPE statement in the SQL standard also defines
615       other forms that are not implemented in PostgreSQL.
616
617       The ability to create a composite type with zero attributes is a
618       PostgreSQL-specific deviation from the standard (analogous to the same
619       case in CREATE TABLE).
620

SEE ALSO

622       ALTER TYPE (ALTER_TYPE(7)), CREATE DOMAIN (CREATE_DOMAIN(7)), CREATE
623       FUNCTION (CREATE_FUNCTION(7)), DROP TYPE (DROP_TYPE(7))
624
625
626
627PostgreSQL 16.1                      2023                       CREATE TYPE(7)
Impressum