1CREATE TYPE(7) PostgreSQL 14.3 Documentation CREATE TYPE(7)
2
6 CREATE_TYPE - define a new data type
7
9 CREATE TYPE name AS
10 ( [ attribute_name data_type [ COLLATE collation ] [, ... ] ] )
11 CREATE TYPE name AS ENUM
13 ( [ 'label' [, ... ] ] )
14 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 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 CREATE TYPE name
47
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 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 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 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 To be able to create a composite type, you must have USAGE privilege on
79 all attribute types.
80 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 Range Types
90 The third form of CREATE TYPE creates a new range type, as described in
91 Section 8.17.
92 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 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 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 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 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 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 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 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 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 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 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 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 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 70.2; it's unwise to access it
254 directly.)
255 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 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 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 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 70.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 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 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 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 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 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 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 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 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 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 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
382 name
383 The name (optionally schema-qualified) of a type to be created.
384 attribute_name
386 The name of an attribute (column) for the composite type.
387 data_type
389 The name of an existing data type to become a column of the
390 composite type.
391 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 label
397 A string literal representing the textual label associated with one
398 value of an enum type.
399 subtype
401 The name of the element type that the range type will represent
402 ranges of.
403 subtype_operator_class
405 The name of a b-tree operator class for the subtype.
406 canonical_function
408 The name of the canonicalization function for the range type.
409 subtype_diff_function
411 The name of a difference function for the subtype.
412 multirange_type_name
414 The name of the corresponding multirange type.
415 input_function
417 The name of a function that converts data from the type's external
418 textual form to its internal form.
419 output_function
421 The name of a function that converts data from the type's internal
422 form to its external textual form.
423 receive_function
425 The name of a function that converts data from the type's external
426 binary form to its internal form.
427 send_function
429 The name of a function that converts data from the type's internal
430 form to its external binary form.
431 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 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 analyze_function
441 The name of a function that performs statistical analysis for the
442 data type.
443 subscript_function
445 The name of a function that defines what subscripting a value of
446 the data type does.
447 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 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 storage
458 The storage strategy for the data type. If specified, must be
459 plain, external, extended, or main; the default is plain.
460 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 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 52.63. You may also choose other ASCII
472 characters in order to create custom categories.
473 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 default
481 The default value for the data type. If this is omitted, the
482 default is null.
483 element
485 The type being created is an array; this specifies the type of the
486 array elements.
487 delimiter
489 The delimiter character to be used between values in arrays made of
490 this type.
491 collatable
493 True if this type's operations can use collation information. The
494 default is false.
495
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 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 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 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
534 This example creates a composite type and uses it in a function
535 definition:
536 CREATE TYPE compfoo AS (f1 int, f2 text);
538 CREATE FUNCTION getfoo() RETURNS SETOF compfoo AS $$
540 SELECT fooid, fooname FROM foo
541 $$ LANGUAGE SQL;
542 This example creates an enumerated type and uses it in a table
544 definition:
545 CREATE TYPE bug_status AS ENUM ('new', 'open', 'closed');
547 CREATE TABLE bug (
549 id serial,
550 description text,
551 status bug_status
552 );
553 This example creates a range type:
555 CREATE TYPE float8_range AS RANGE (subtype = float8, subtype_diff = float8mi);
557 This example creates the base data type box and then uses the type in a
559 table definition:
560 CREATE TYPE box;
562 CREATE FUNCTION my_box_in_function(cstring) RETURNS box AS ... ;
564 CREATE FUNCTION my_box_out_function(box) RETURNS cstring AS ... ;
565 CREATE TYPE box (
567 INTERNALLENGTH = 16,
568 INPUT = my_box_in_function,
569 OUTPUT = my_box_out_function
570 );
571 CREATE TABLE myboxes (
573 id integer,
574 description box
575 );
576 If the internal structure of box were an array of four float4 elements,
578 we might instead use:
579 CREATE TYPE box (
581 INTERNALLENGTH = 16,
582 INPUT = my_box_in_function,
583 OUTPUT = my_box_out_function,
584 ELEMENT = float4
585 );
586 which would allow a box value's component numbers to be accessed by
588 subscripting. Otherwise the type behaves the same as before.
589 This example creates a large object type and uses it in a table
591 definition:
592 CREATE TYPE bigobj (
594 INPUT = lo_filein, OUTPUT = lo_fileout,
595 INTERNALLENGTH = VARIABLE
596 );
597 CREATE TABLE big_objs (
598 id integer,
599 obj bigobj
600 );
601 More examples, including suitable input and output functions, are in
603 Section 38.13.
604
606 The first form of the CREATE TYPE command, which creates a composite
607 type, conforms to the SQL standard. The other forms are PostgreSQL
608 extensions. The CREATE TYPE statement in the SQL standard also defines
609 other forms that are not implemented in PostgreSQL.
610 The ability to create a composite type with zero attributes is a
612 PostgreSQL-specific deviation from the standard (analogous to the same
613 case in CREATE TABLE).
614
616 ALTER TYPE (ALTER_TYPE(7)), CREATE DOMAIN (CREATE_DOMAIN(7)), CREATE
617 FUNCTION (CREATE_FUNCTION(7)), DROP TYPE (DROP_TYPE(7))
618PostgreSQL 14.3 2022 CREATE TYPE(7)