Debian Codegraph @

doc/src/sgml/ref/create_type.sgml

raw
1 <!--
2 doc/src/sgml/ref/create_type.sgml
3 PostgreSQL documentation
4 -->
5
6 <refentry id="sql-createtype">
7 <indexterm zone="sql-createtype">
8 <primary>CREATE TYPE</primary>
9 </indexterm>
10
11 <refmeta>
12 <refentrytitle>CREATE TYPE</refentrytitle>
13 <manvolnum>7</manvolnum>
14 <refmiscinfo>SQL - Language Statements</refmiscinfo>
15 </refmeta>
16
17 <refnamediv>
18 <refname>CREATE TYPE</refname>
19 <refpurpose>define a new data type</refpurpose>
20 </refnamediv>
21
22 <refsynopsisdiv>
23 <synopsis>
24 CREATE TYPE <replaceable class="parameter">name</replaceable> AS
25 ( [ <replaceable class="parameter">attribute_name</replaceable> <replaceable class="parameter">data_type</replaceable> [ COLLATE <replaceable>collation</replaceable> ] [, ... ] ] )
26
27 CREATE TYPE <replaceable class="parameter">name</replaceable> AS ENUM
28 ( [ '<replaceable class="parameter">label</replaceable>' [, ... ] ] )
29
30 CREATE TYPE <replaceable class="parameter">name</replaceable> AS RANGE (
31 SUBTYPE = <replaceable class="parameter">subtype</replaceable>
32 [ , SUBTYPE_OPCLASS = <replaceable class="parameter">subtype_operator_class</replaceable> ]
33 [ , COLLATION = <replaceable class="parameter">collation</replaceable> ]
34 [ , CANONICAL = <replaceable class="parameter">canonical_function</replaceable> ]
35 [ , SUBTYPE_DIFF = <replaceable class="parameter">subtype_diff_function</replaceable> ]
36 [ , MULTIRANGE_TYPE_NAME = <replaceable class="parameter">multirange_type_name</replaceable> ]
37 )
38
39 CREATE TYPE <replaceable class="parameter">name</replaceable> (
40 INPUT = <replaceable class="parameter">input_function</replaceable>,
41 OUTPUT = <replaceable class="parameter">output_function</replaceable>
42 [ , RECEIVE = <replaceable class="parameter">receive_function</replaceable> ]
43 [ , SEND = <replaceable class="parameter">send_function</replaceable> ]
44 [ , TYPMOD_IN = <replaceable class="parameter">type_modifier_input_function</replaceable> ]
45 [ , TYPMOD_OUT = <replaceable class="parameter">type_modifier_output_function</replaceable> ]
46 [ , ANALYZE = <replaceable class="parameter">analyze_function</replaceable> ]
47 [ , SUBSCRIPT = <replaceable class="parameter">subscript_function</replaceable> ]
48 [ , INTERNALLENGTH = { <replaceable class="parameter">internallength</replaceable> | VARIABLE } ]
49 [ , PASSEDBYVALUE ]
50 [ , ALIGNMENT = <replaceable class="parameter">alignment</replaceable> ]
51 [ , STORAGE = <replaceable class="parameter">storage</replaceable> ]
52 [ , LIKE = <replaceable class="parameter">like_type</replaceable> ]
53 [ , CATEGORY = <replaceable class="parameter">category</replaceable> ]
54 [ , PREFERRED = <replaceable class="parameter">preferred</replaceable> ]
55 [ , DEFAULT = <replaceable class="parameter">default</replaceable> ]
56 [ , ELEMENT = <replaceable class="parameter">element</replaceable> ]
57 [ , DELIMITER = <replaceable class="parameter">delimiter</replaceable> ]
58 [ , COLLATABLE = <replaceable class="parameter">collatable</replaceable> ]
59 )
60
61 CREATE TYPE <replaceable class="parameter">name</replaceable>
62 </synopsis>
63 </refsynopsisdiv>
64
65 <refsect1>
66 <title>Description</title>
67
68 <para>
69 <command>CREATE TYPE</command> registers a new data type for use in
70 the current database. The user who defines a type becomes its
71 owner.
72 </para>
73
74 <para>
75 If a schema name is given then the type is created in the specified
76 schema. Otherwise it is created in the current schema. The type
77 name must be distinct from the name of any existing type or domain
78 in the same schema. (Because tables have associated data types,
79 the type name must also be distinct from the name of any existing
80 table in the same schema.)
81 </para>
82
83 <para>
84 There are five forms of <command>CREATE TYPE</command>, as shown in the
85 syntax synopsis above. They respectively create a <firstterm>composite
86 type</firstterm>, an <firstterm>enum type</firstterm>, a <firstterm>range type</firstterm>, a
87 <firstterm>base type</firstterm>, or a <firstterm>shell type</firstterm>. The first four
88 of these are discussed in turn below. A shell type is simply a placeholder
89 for a type to be defined later; it is created by issuing <command>CREATE
90 TYPE</command> with no parameters except for the type name. Shell types
91 are needed as forward references when creating range types and base types,
92 as discussed in those sections.
93 </para>
94
95 <refsect2>
96 <title>Composite Types</title>
97
98 <para>
99 The first form of <command>CREATE TYPE</command>
100 creates a composite type.
101 The composite type is specified by a list of attribute names and data types.
102 An attribute's collation can be specified too, if its data type is
103 collatable. A composite type is essentially the same as the row type
104 of a table, but using <command>CREATE TYPE</command> avoids the need to
105 create an actual table when all that is wanted is to define a type.
106 A stand-alone composite type is useful, for example, as the argument or
107 return type of a function.
108 </para>
109
110 <para>
111 To be able to create a composite type, you must
112 have <literal>USAGE</literal> privilege on all attribute types.
113 </para>
114 </refsect2>
115
116 <refsect2 id="sql-createtype-enum">
117 <title>Enumerated Types</title>
118
119 <para>
120 The second form of <command>CREATE TYPE</command> creates an enumerated
121 (enum) type, as described in <xref linkend="datatype-enum"/>.
122 Enum types take a list of quoted labels, each of which
123 must be less than <symbol>NAMEDATALEN</symbol> bytes long (64 bytes in a
124 standard <productname>PostgreSQL</productname> build). (It is possible to
125 create an enumerated type with zero labels, but such a type cannot be used
126 to hold values before at least one label is added using <link
127 linkend="sql-altertype"><command>ALTER TYPE</command></link>.)
128 </para>
129 </refsect2>
130
131 <refsect2 id="sql-createtype-range">
132 <title>Range Types</title>
133
134 <para>
135 The third form of <command>CREATE TYPE</command> creates a new
136 range type, as described in <xref linkend="rangetypes"/>.
137 </para>
138
139 <para>
140 The range type's <replaceable class="parameter">subtype</replaceable> can
141 be any type with an associated b-tree operator class (to determine the
142 ordering of values for the range type). Normally the subtype's default
143 b-tree operator class is used to determine ordering; to use a non-default
144 operator class, specify its name with <replaceable
145 class="parameter">subtype_opclass</replaceable>. If the subtype is
146 collatable, and you want to use a non-default collation in the range's
147 ordering, specify the desired collation with the <replaceable
148 class="parameter">collation</replaceable> option.
149 </para>
150
151 <para>
152 The optional <replaceable class="parameter">canonical</replaceable>
153 function must take one argument of the range type being defined, and
154 return a value of the same type. This is used to convert range values
155 to a canonical form, when applicable. See <xref
156 linkend="rangetypes-defining"/> for more information. Creating a
157 <replaceable class="parameter">canonical</replaceable> function
158 is a bit tricky, since it must be defined before the range type can be
159 declared. To do this, you must first create a shell type, which is a
160 placeholder type that has no properties except a name and an
161 owner. This is done by issuing the command <literal>CREATE TYPE
162 <replaceable>name</replaceable></literal>, with no additional parameters. Then
163 the function can be declared using the shell type as argument and result,
164 and finally the range type can be declared using the same name. This
165 automatically replaces the shell type entry with a valid range type.
166 </para>
167
168 <para>
169 The optional <replaceable class="parameter">subtype_diff</replaceable>
170 function must take two values of the
171 <replaceable class="parameter">subtype</replaceable> type as argument,
172 and return a <type>double precision</type> value representing the
173 difference between the two given values. While this is optional,
174 providing it allows much greater efficiency of GiST indexes on columns of
175 the range type. See <xref linkend="rangetypes-defining"/> for more
176 information.
177 </para>
178
179 <para>
180 The optional <replaceable class="parameter">multirange_type_name</replaceable>
181 parameter specifies the name of the corresponding multirange type. If not
182 specified, this name is chosen automatically as follows.
183 If the range type name contains the substring <literal>range</literal>, then
184 the multirange type name is formed by replacement of the <literal>range</literal>
185 substring with <literal>multirange</literal> in the range
186 type name. Otherwise, the multirange type name is formed by appending a
187 <literal>_multirange</literal> suffix to the range type name.
188 </para>
189
190 <para>
191 To be able to create a range type, you must have <literal>USAGE</literal>
192 privilege on the subtype.
193 </para>
194 </refsect2>
195
196 <refsect2>
197 <title>Base Types</title>
198
199 <para>
200 The fourth form of <command>CREATE TYPE</command> creates a new base type
201 (scalar type). To create a new base type, you must be a superuser.
202 (This restriction is made because an erroneous type definition could
203 confuse or even crash the server.)
204 </para>
205
206 <para>
207 The parameters can appear in any order, not only that
208 illustrated above, and most are optional. You must register
209 two or more functions (using <command>CREATE FUNCTION</command>) before
210 defining the type. The support functions
211 <replaceable class="parameter">input_function</replaceable> and
212 <replaceable class="parameter">output_function</replaceable>
213 are required, while the functions
214 <replaceable class="parameter">receive_function</replaceable>,
215 <replaceable class="parameter">send_function</replaceable>,
216 <replaceable class="parameter">type_modifier_input_function</replaceable>,
217 <replaceable class="parameter">type_modifier_output_function</replaceable>,
218 <replaceable class="parameter">analyze_function</replaceable>, and
219 <replaceable class="parameter">subscript_function</replaceable>
220 are optional. Generally these functions have to be coded in C
221 or another low-level language.
222 </para>
223
224 <para>
225 The <replaceable class="parameter">input_function</replaceable>
226 converts the type's external textual representation to the internal
227 representation used by the operators and functions defined for the type.
228 <replaceable class="parameter">output_function</replaceable>
229 performs the reverse transformation. The input function can be
230 declared as taking one argument of type <type>cstring</type>,
231 or as taking three arguments of types
232 <type>cstring</type>, <type>oid</type>, <type>integer</type>.
233 The first argument is the input text as a C string, the second
234 argument is the type's own OID (except for array types, which instead
235 receive their element type's OID),
236 and the third is the <literal>typmod</literal> of the destination column, if known
237 (-1 will be passed if not).
238 The input function must return a value of the data type itself.
239 Usually, an input function should be declared STRICT; if it is not,
240 it will be called with a NULL first parameter when reading a NULL
241 input value. The function must still return NULL in this case, unless
242 it raises an error.
243 (This case is mainly meant to support domain input functions, which
244 might need to reject NULL inputs.)
245 The output function must be
246 declared as taking one argument of the new data type.
247 The output function must return type <type>cstring</type>.
248 Output functions are not invoked for NULL values.
249 </para>
250
251 <para>
252 The optional <replaceable class="parameter">receive_function</replaceable>
253 converts the type's external binary representation to the internal
254 representation. If this function is not supplied, the type cannot
255 participate in binary input. The binary representation should be
256 chosen to be cheap to convert to internal form, while being reasonably
257 portable. (For example, the standard integer data types use network
258 byte order as the external binary representation, while the internal
259 representation is in the machine's native byte order.) The receive
260 function should perform adequate checking to ensure that the value is
261 valid.
262 The receive function can be declared as taking one argument of type
263 <type>internal</type>, or as taking three arguments of types
264 <type>internal</type>, <type>oid</type>, <type>integer</type>.
265 The first argument is a pointer to a <type>StringInfo</type> buffer
266 holding the received byte string; the optional arguments are the
267 same as for the text input function.
268 The receive function must return a value of the data type itself.
269 Usually, a receive function should be declared STRICT; if it is not,
270 it will be called with a NULL first parameter when reading a NULL
271 input value. The function must still return NULL in this case, unless
272 it raises an error.
273 (This case is mainly meant to support domain receive functions, which
274 might need to reject NULL inputs.)
275 Similarly, the optional
276 <replaceable class="parameter">send_function</replaceable> converts
277 from the internal representation to the external binary representation.
278 If this function is not supplied, the type cannot participate in binary
279 output. The send function must be
280 declared as taking one argument of the new data type.
281 The send function must return type <type>bytea</type>.
282 Send functions are not invoked for NULL values.
283 </para>
284
285 <para>
286 You should at this point be wondering how the input and output functions
287 can be declared to have results or arguments of the new type, when they
288 have to be created before the new type can be created. The answer is that
289 the type should first be defined as a <firstterm>shell type</firstterm>, which is a
290 placeholder type that has no properties except a name and an owner. This
291 is done by issuing the command <literal>CREATE TYPE
292 <replaceable>name</replaceable></literal>, with no additional parameters. Then the
293 C I/O functions can be defined referencing the shell type. Finally,
294 <command>CREATE TYPE</command> with a full definition replaces the shell entry
295 with a complete, valid type definition, after which the new type can be
296 used normally.
297 </para>
298
299 <para>
300 The optional
301 <replaceable class="parameter">type_modifier_input_function</replaceable>
302 and <replaceable class="parameter">type_modifier_output_function</replaceable>
303 are needed if the type supports modifiers, that is optional constraints
304 attached to a type declaration, such as <literal>char(5)</literal> or
305 <literal>numeric(30,2)</literal>. <productname>PostgreSQL</productname> allows
306 user-defined types to take one or more simple constants or identifiers as
307 modifiers. However, this information must be capable of being packed into a
308 single non-negative integer value for storage in the system catalogs. The
309 <replaceable class="parameter">type_modifier_input_function</replaceable>
310 is passed the declared modifier(s) in the form of a <type>cstring</type>
311 array. It must check the values for validity (throwing an error if they
312 are wrong), and if they are correct, return a single non-negative
313 <type>integer</type> value that will be stored as the column <quote>typmod</quote>.
314 Type modifiers will be rejected if the type does not have a
315 <replaceable class="parameter">type_modifier_input_function</replaceable>.
316 The <replaceable class="parameter">type_modifier_output_function</replaceable>
317 converts the internal integer typmod value back to the correct form for
318 user display. It must return a <type>cstring</type> value that is the exact
319 string to append to the type name; for example <type>numeric</type>'s
320 function might return <literal>(30,2)</literal>.
321 It is allowed to omit the
322 <replaceable class="parameter">type_modifier_output_function</replaceable>,
323 in which case the default display format is just the stored typmod integer
324 value enclosed in parentheses.
325 </para>
326
327 <para>
328 The optional <replaceable class="parameter">analyze_function</replaceable>
329 performs type-specific statistics collection for columns of the data type.
330 By default, <command>ANALYZE</command> will attempt to gather statistics using
331 the type's <quote>equals</quote> and <quote>less-than</quote> operators, if there
332 is a default b-tree operator class for the type. For non-scalar types
333 this behavior is likely to be unsuitable, so it can be overridden by
334 specifying a custom analysis function. The analysis function must be
335 declared to take a single argument of type <type>internal</type>, and return
336 a <type>boolean</type> result. The detailed API for analysis functions appears
337 in <filename>src/include/commands/vacuum.h</filename>.
338 </para>
339
340 <para>
341 The optional <replaceable class="parameter">subscript_function</replaceable>
342 allows the data type to be subscripted in SQL commands. Specifying this
343 function does not cause the type to be considered a <quote>true</quote>
344 array type; for example, it will not be a candidate for the result type
345 of <literal>ARRAY[]</literal> constructs. But if subscripting a value
346 of the type is a natural notation for extracting data from it, then
347 a <replaceable class="parameter">subscript_function</replaceable> can
348 be written to define what that means. The subscript function must be
349 declared to take a single argument of type <type>internal</type>, and
350 return an <type>internal</type> result, which is a pointer to a struct
351 of methods (functions) that implement subscripting.
352 The detailed API for subscript functions appears
353 in <filename>src/include/nodes/subscripting.h</filename>.
354 It may also be useful to read the array implementation
355 in <filename>src/backend/utils/adt/arraysubs.c</filename>,
356 or the simpler code
357 in <filename>contrib/hstore/hstore_subs.c</filename>.
358 Additional information appears in
359 <xref linkend="sql-createtype-array"/> below.
360 </para>
361
362 <para>
363 While the details of the new type's internal representation are only
364 known to the I/O functions and other functions you create to work with
365 the type, there are several properties of the internal representation
366 that must be declared to <productname>PostgreSQL</productname>.
367 Foremost of these is
368 <replaceable class="parameter">internallength</replaceable>.
369 Base data types can be fixed-length, in which case
370 <replaceable class="parameter">internallength</replaceable> is a
371 positive integer, or variable-length, indicated by setting
372 <replaceable class="parameter">internallength</replaceable>
373 to <literal>VARIABLE</literal>. (Internally, this is represented
374 by setting <literal>typlen</literal> to -1.) The internal representation of all
375 variable-length types must start with a 4-byte integer giving the total
376 length of this value of the type. (Note that the length field is often
377 encoded, as described in <xref linkend="storage-toast"/>; it's unwise
378 to access it directly.)
379 </para>
380
381 <para>
382 The optional flag <literal>PASSEDBYVALUE</literal> indicates that
383 values of this data type are passed by value, rather than by
384 reference. Types passed by value must be fixed-length, and their internal
385 representation cannot be larger than the size of the <type>Datum</type> type
386 (4 bytes on some machines, 8 bytes on others).
387 </para>
388
389 <para>
390 The <replaceable class="parameter">alignment</replaceable> parameter
391 specifies the storage alignment required for the data type. The
392 allowed values equate to alignment on 1, 2, 4, or 8 byte boundaries.
393 Note that variable-length types must have an alignment of at least
394 4, since they necessarily contain an <type>int4</type> as their first component.
395 </para>
396
397 <para>
398 The <replaceable class="parameter">storage</replaceable> parameter
399 allows selection of storage strategies for variable-length data
400 types. (Only <literal>plain</literal> is allowed for fixed-length
401 types.) <literal>plain</literal> specifies that data of the type
402 will always be stored in-line and not compressed.
403 <literal>extended</literal> specifies that the system will first
404 try to compress a long data value, and will move the value out of
405 the main table row if it's still too long.
406 <literal>external</literal> allows the value to be moved out of the
407 main table, but the system will not try to compress it.
408 <literal>main</literal> allows compression, but discourages moving
409 the value out of the main table. (Data items with this storage
410 strategy might still be moved out of the main table if there is no
411 other way to make a row fit, but they will be kept in the main
412 table preferentially over <literal>extended</literal> and
413 <literal>external</literal> items.)
414 </para>
415
416 <para>
417 All <replaceable class="parameter">storage</replaceable> values other
418 than <literal>plain</literal> imply that the functions of the data type
419 can handle values that have been <firstterm>toasted</firstterm>, as described
420 in <xref linkend="storage-toast"/> and <xref linkend="xtypes-toast"/>.
421 The specific other value given merely determines the default TOAST
422 storage strategy for columns of a toastable data type; users can pick
423 other strategies for individual columns using <literal>ALTER TABLE
424 SET STORAGE</literal>.
425 </para>
426
427 <para>
428 The <replaceable class="parameter">like_type</replaceable> parameter
429 provides an alternative method for specifying the basic representation
430 properties of a data type: copy them from some existing type. The values of
431 <replaceable class="parameter">internallength</replaceable>,
432 <replaceable class="parameter">passedbyvalue</replaceable>,
433 <replaceable class="parameter">alignment</replaceable>, and
434 <replaceable class="parameter">storage</replaceable> are copied from the
435 named type. (It is possible, though usually undesirable, to override
436 some of these values by specifying them along with the <literal>LIKE</literal>
437 clause.) Specifying representation this way is especially useful when
438 the low-level implementation of the new type <quote>piggybacks</quote> on an
439 existing type in some fashion.
440 </para>
441
442 <para>
443 The <replaceable class="parameter">category</replaceable> and
444 <replaceable class="parameter">preferred</replaceable> parameters can be
445 used to help control which implicit cast will be applied in ambiguous
446 situations. Each data type belongs to a category named by a single ASCII
447 character, and each type is either <quote>preferred</quote> or not within its
448 category. The parser will prefer casting to preferred types (but only from
449 other types within the same category) when this rule is helpful in
450 resolving overloaded functions or operators. For more details see <xref
451 linkend="typeconv"/>. For types that have no implicit casts to or from any
452 other types, it is sufficient to leave these settings at the defaults.
453 However, for a group of related types that have implicit casts, it is often
454 helpful to mark them all as belonging to a category and select one or two
455 of the <quote>most general</quote> types as being preferred within the category.
456 The <replaceable class="parameter">category</replaceable> parameter is
457 especially useful when adding a user-defined type to an existing built-in
458 category, such as the numeric or string types. However, it is also
459 possible to create new entirely-user-defined type categories. Select any
460 ASCII character other than an upper-case letter to name such a category.
461 </para>
462
463 <para>
464 A default value can be specified, in case a user wants columns of the
465 data type to default to something other than the null value.
466 Specify the default with the <literal>DEFAULT</literal> key word.
467 (Such a default can be overridden by an explicit <literal>DEFAULT</literal>
468 clause attached to a particular column.)
469 </para>
470
471 <para>
472 To indicate that a type is a fixed-length array type,
473 specify the type of the array
474 elements using the <literal>ELEMENT</literal> key word. For example, to
475 define an array of 4-byte integers (<type>int4</type>), specify
476 <literal>ELEMENT = int4</literal>. For more details,
477 see <xref linkend="sql-createtype-array"/> below.
478 </para>
479
480 <para>
481 To indicate the delimiter to be used between values in the external
482 representation of arrays of this type, <replaceable
483 class="parameter">delimiter</replaceable> can be
484 set to a specific character. The default delimiter is the comma
485 (<literal>,</literal>). Note that the delimiter is associated
486 with the array element type, not the array type itself.
487 </para>
488
489 <para>
490 If the optional Boolean
491 parameter <replaceable class="parameter">collatable</replaceable>
492 is true, column definitions and expressions of the type may carry
493 collation information through use of
494 the <literal>COLLATE</literal> clause. It is up to the
495 implementations of the functions operating on the type to actually
496 make use of the collation information; this does not happen
497 automatically merely by marking the type collatable.
498 </para>
499 </refsect2>
500
501 <refsect2 id="sql-createtype-array" xreflabel="Array Types">
502 <title>Array Types</title>
503
504 <para>
505 Whenever a user-defined type is created,
506 <productname>PostgreSQL</productname> automatically creates an
507 associated array type, whose name consists of the element type's
508 name prepended with an underscore, and truncated if necessary to keep
509 it less than <symbol>NAMEDATALEN</symbol> bytes long. (If the name
510 so generated collides with an existing type name, the process is
511 repeated until a non-colliding name is found.)
512 This implicitly-created array type is variable length and uses the
513 built-in input and output functions <literal>array_in</literal> and
514 <literal>array_out</literal>. Furthermore, this type is what the system
515 uses for constructs such as <literal>ARRAY[]</literal> over the
516 user-defined type. The array type tracks any changes in its
517 element type's owner or schema, and is dropped if the element type is.
518 </para>
519
520 <para>
521 You might reasonably ask why there is an <option>ELEMENT</option>
522 option, if the system makes the correct array type automatically.
523 The main case where it's useful to use <option>ELEMENT</option> is when you are
524 making a fixed-length type that happens to be internally an array of a number of
525 identical things, and you want to allow these things to be accessed
526 directly by subscripting, in addition to whatever operations you plan
527 to provide for the type as a whole. For example, type <type>point</type>
528 is represented as just two floating-point numbers, which can be accessed
529 using <literal>point[0]</literal> and <literal>point[1]</literal>.
530 Note that
531 this facility only works for fixed-length types whose internal form
532 is exactly a sequence of identical fixed-length fields.
533 For historical reasons (i.e., this is clearly wrong but it's far too
534 late to change it), subscripting of fixed-length array types starts from
535 zero, rather than from one as for variable-length arrays.
536 </para>
537
538 <para>
539 Specifying the <option>SUBSCRIPT</option> option allows a data type to
540 be subscripted, even though the system does not otherwise regard it as
541 an array type. The behavior just described for fixed-length arrays is
542 actually implemented by the <option>SUBSCRIPT</option> handler
543 function <function>raw_array_subscript_handler</function>, which is
544 used automatically if you specify <option>ELEMENT</option> for a
545 fixed-length type without also writing <option>SUBSCRIPT</option>.
546 </para>
547
548 <para>
549 When specifying a custom <option>SUBSCRIPT</option> function, it is
550 not necessary to specify <option>ELEMENT</option> unless
551 the <option>SUBSCRIPT</option> handler function needs to
552 consult <structfield>typelem</structfield> to find out what to return.
553 Be aware that specifying <option>ELEMENT</option> causes the system to
554 assume that the new type contains, or is somehow physically dependent on,
555 the element type; thus for example changing properties of the element
556 type won't be allowed if there are any columns of the dependent type.
557 </para>
558 </refsect2>
559 </refsect1>
560
561 <refsect1>
562 <title>Parameters</title>
563
564 <variablelist>
565 <varlistentry>
566 <term><replaceable class="parameter">name</replaceable></term>
567 <listitem>
568 <para>
569 The name (optionally schema-qualified) of a type to be created.
570 </para>
571 </listitem>
572 </varlistentry>
573
574 <varlistentry>
575 <term><replaceable class="parameter">attribute_name</replaceable></term>
576 <listitem>
577 <para>
578 The name of an attribute (column) for the composite type.
579 </para>
580 </listitem>
581 </varlistentry>
582
583 <varlistentry>
584 <term><replaceable class="parameter">data_type</replaceable></term>
585 <listitem>
586 <para>
587 The name of an existing data type to become a column of the
588 composite type.
589 </para>
590 </listitem>
591 </varlistentry>
592
593 <varlistentry>
594 <term><replaceable class="parameter">collation</replaceable></term>
595 <listitem>
596 <para>
597 The name of an existing collation to be associated with a column of
598 a composite type, or with a range type.
599 </para>
600 </listitem>
601 </varlistentry>
602
603 <varlistentry>
604 <term><replaceable class="parameter">label</replaceable></term>
605 <listitem>
606 <para>
607 A string literal representing the textual label associated with
608 one value of an enum type.
609 </para>
610 </listitem>
611 </varlistentry>
612
613 <varlistentry>
614 <term><replaceable class="parameter">subtype</replaceable></term>
615 <listitem>
616 <para>
617 The name of the element type that the range type will represent ranges
618 of.
619 </para>
620 </listitem>
621 </varlistentry>
622
623 <varlistentry>
624 <term><replaceable class="parameter">subtype_operator_class</replaceable></term>
625 <listitem>
626 <para>
627 The name of a b-tree operator class for the subtype.
628 </para>
629 </listitem>
630 </varlistentry>
631
632 <varlistentry>
633 <term><replaceable class="parameter">canonical_function</replaceable></term>
634 <listitem>
635 <para>
636 The name of the canonicalization function for the range type.
637 </para>
638 </listitem>
639 </varlistentry>
640
641 <varlistentry>
642 <term><replaceable class="parameter">subtype_diff_function</replaceable></term>
643 <listitem>
644 <para>
645 The name of a difference function for the subtype.
646 </para>
647 </listitem>
648 </varlistentry>
649
650 <varlistentry>
651 <term><replaceable class="parameter">multirange_type_name</replaceable></term>
652 <listitem>
653 <para>
654 The name of the corresponding multirange type.
655 </para>
656 </listitem>
657 </varlistentry>
658
659 <varlistentry>
660 <term><replaceable class="parameter">input_function</replaceable></term>
661 <listitem>
662 <para>
663 The name of a function that converts data from the type's
664 external textual form to its internal form.
665 </para>
666 </listitem>
667 </varlistentry>
668
669 <varlistentry>
670 <term><replaceable class="parameter">output_function</replaceable></term>
671 <listitem>
672 <para>
673 The name of a function that converts data from the type's
674 internal form to its external textual form.
675 </para>
676 </listitem>
677 </varlistentry>
678
679 <varlistentry>
680 <term><replaceable class="parameter">receive_function</replaceable></term>
681 <listitem>
682 <para>
683 The name of a function that converts data from the type's
684 external binary form to its internal form.
685 </para>
686 </listitem>
687 </varlistentry>
688
689 <varlistentry>
690 <term><replaceable class="parameter">send_function</replaceable></term>
691 <listitem>
692 <para>
693 The name of a function that converts data from the type's
694 internal form to its external binary form.
695 </para>
696 </listitem>
697 </varlistentry>
698
699 <varlistentry>
700 <term><replaceable class="parameter">type_modifier_input_function</replaceable></term>
701 <listitem>
702 <para>
703 The name of a function that converts an array of modifier(s) for the type
704 into internal form.
705 </para>
706 </listitem>
707 </varlistentry>
708
709 <varlistentry>
710 <term><replaceable class="parameter">type_modifier_output_function</replaceable></term>
711 <listitem>
712 <para>
713 The name of a function that converts the internal form of the type's
714 modifier(s) to external textual form.
715 </para>
716 </listitem>
717 </varlistentry>
718
719 <varlistentry>
720 <term><replaceable class="parameter">analyze_function</replaceable></term>
721 <listitem>
722 <para>
723 The name of a function that performs statistical analysis for the
724 data type.
725 </para>
726 </listitem>
727 </varlistentry>
728
729 <varlistentry>
730 <term><replaceable class="parameter">subscript_function</replaceable></term>
731 <listitem>
732 <para>
733 The name of a function that defines what subscripting a value of the
734 data type does.
735 </para>
736 </listitem>
737 </varlistentry>
738
739 <varlistentry>
740 <term><replaceable class="parameter">internallength</replaceable></term>
741 <listitem>
742 <para>
743 A numeric constant that specifies the length in bytes of the new
744 type's internal representation. The default assumption is that
745 it is variable-length.
746 </para>
747 </listitem>
748 </varlistentry>
749
750 <varlistentry>
751 <term><replaceable class="parameter">alignment</replaceable></term>
752 <listitem>
753 <para>
754 The storage alignment requirement of the data type. If specified,
755 it must be <literal>char</literal>, <literal>int2</literal>,
756 <literal>int4</literal>, or <literal>double</literal>; the
757 default is <literal>int4</literal>.
758 </para>
759 </listitem>
760 </varlistentry>
761
762 <varlistentry>
763 <term><replaceable class="parameter">storage</replaceable></term>
764 <listitem>
765 <para>
766 The storage strategy for the data type. If specified, must be
767 <literal>plain</literal>, <literal>external</literal>,
768 <literal>extended</literal>, or <literal>main</literal>; the
769 default is <literal>plain</literal>.
770 </para>
771 </listitem>
772 </varlistentry>
773
774 <varlistentry>
775 <term><replaceable class="parameter">like_type</replaceable></term>
776 <listitem>
777 <para>
778 The name of an existing data type that the new type will have the
779 same representation as. The values of
780 <replaceable class="parameter">internallength</replaceable>,
781 <replaceable class="parameter">passedbyvalue</replaceable>,
782 <replaceable class="parameter">alignment</replaceable>, and
783 <replaceable class="parameter">storage</replaceable>
784 are copied from that type, unless overridden by explicit
785 specification elsewhere in this <command>CREATE TYPE</command> command.
786 </para>
787 </listitem>
788 </varlistentry>
789
790 <varlistentry>
791 <term><replaceable class="parameter">category</replaceable></term>
792 <listitem>
793 <para>
794 The category code (a single ASCII character) for this type.
795 The default is <literal>'U'</literal> for <quote>user-defined type</quote>.
796 Other standard category codes can be found in
797 <xref linkend="catalog-typcategory-table"/>. You may also choose
798 other ASCII characters in order to create custom categories.
799 </para>
800 </listitem>
801 </varlistentry>
802
803 <varlistentry>
804 <term><replaceable class="parameter">preferred</replaceable></term>
805 <listitem>
806 <para>
807 True if this type is a preferred type within its type category,
808 else false. The default is false. Be very careful about creating
809 a new preferred type within an existing type category, as this
810 could cause surprising changes in behavior.
811 </para>
812 </listitem>
813 </varlistentry>
814
815 <varlistentry>
816 <term><replaceable class="parameter">default</replaceable></term>
817 <listitem>
818 <para>
819 The default value for the data type. If this is omitted, the
820 default is null.
821 </para>
822 </listitem>
823 </varlistentry>
824
825 <varlistentry>
826 <term><replaceable class="parameter">element</replaceable></term>
827 <listitem>
828 <para>
829 The type being created is an array; this specifies the type of
830 the array elements.
831 </para>
832 </listitem>
833 </varlistentry>
834
835 <varlistentry>
836 <term><replaceable class="parameter">delimiter</replaceable></term>
837 <listitem>
838 <para>
839 The delimiter character to be used between values in arrays made
840 of this type.
841 </para>
842 </listitem>
843 </varlistentry>
844
845 <varlistentry>
846 <term><replaceable class="parameter">collatable</replaceable></term>
847 <listitem>
848 <para>
849 True if this type's operations can use collation information.
850 The default is false.
851 </para>
852 </listitem>
853 </varlistentry>
854 </variablelist>
855 </refsect1>
856
857 <refsect1 id="sql-createtype-notes">
858 <title>Notes</title>
859
860 <para>
861 Because there are no restrictions on use of a data type once it's been
862 created, creating a base type or range type is tantamount to granting
863 public execute permission on the functions mentioned in the type definition.
864 This is usually
865 not an issue for the sorts of functions that are useful in a type
866 definition. But you might want to think twice before designing a type
867 in a way that would require <quote>secret</quote> information to be used
868 while converting it to or from external form.
869 </para>
870
871 <para>
872 Before <productname>PostgreSQL</productname> version 8.3, the name of
873 a generated array type was always exactly the element type's name with one
874 underscore character (<literal>_</literal>) prepended. (Type names were
875 therefore restricted in length to one fewer character than other names.)
876 While this is still usually the case, the array type name may vary from
877 this in case of maximum-length names or collisions with user type names
878 that begin with underscore. Writing code that depends on this convention
879 is therefore deprecated. Instead, use
880 <structname>pg_type</structname>.<structfield>typarray</structfield> to locate the array type
881 associated with a given type.
882 </para>
883
884 <para>
885 It may be advisable to avoid using type and table names that begin with
886 underscore. While the server will change generated array type names to
887 avoid collisions with user-given names, there is still risk of confusion,
888 particularly with old client software that may assume that type names
889 beginning with underscores always represent arrays.
890 </para>
891
892 <para>
893 Before <productname>PostgreSQL</productname> version 8.2, the shell-type
894 creation syntax
895 <literal>CREATE TYPE <replaceable>name</replaceable></literal> did not exist.
896 The way to create a new base type was to create its input function first.
897 In this approach, <productname>PostgreSQL</productname> will first see
898 the name of the new data type as the return type of the input function.
899 The shell type is implicitly created in this situation, and then it
900 can be referenced in the definitions of the remaining I/O functions.
901 This approach still works, but is deprecated and might be disallowed in
902 some future release. Also, to avoid accidentally cluttering
903 the catalogs with shell types as a result of simple typos in function
904 definitions, a shell type will only be made this way when the input
905 function is written in C.
906 </para>
907
908 <para>
909 In <productname>PostgreSQL</productname> version 16 and later,
910 it is desirable for base types' input functions to
911 return <quote>soft</quote> errors using the
912 new <function>errsave()</function>/<function>ereturn()</function>
913 mechanism, rather than throwing <function>ereport()</function>
914 exceptions as in previous versions.
915 See <filename>src/backend/utils/fmgr/README</filename> for more
916 information.
917 </para>
918
919 </refsect1>
920
921 <refsect1>
922 <title>Examples</title>
923
924 <para>
925 This example creates a composite type and uses it in
926 a function definition:
927 <programlisting>
928 CREATE TYPE compfoo AS (f1 int, f2 text);
929
930 CREATE FUNCTION getfoo() RETURNS SETOF compfoo AS $$
931 SELECT fooid, fooname FROM foo
932 $$ LANGUAGE SQL;
933 </programlisting>
934 </para>
935
936 <para>
937 This example creates an enumerated type and uses it in
938 a table definition:
939 <programlisting>
940 CREATE TYPE bug_status AS ENUM ('new', 'open', 'closed');
941
942 CREATE TABLE bug (
943 id serial,
944 description text,
945 status bug_status
946 );
947 </programlisting>
948 </para>
949
950 <para>
951 This example creates a range type:
952 <programlisting>
953 CREATE TYPE float8_range AS RANGE (subtype = float8, subtype_diff = float8mi);
954 </programlisting>
955 </para>
956
957 <para>
958 This example creates the base data type <type>box</type> and then uses the
959 type in a table definition:
960 <programlisting>
961 CREATE TYPE box;
962
963 CREATE FUNCTION my_box_in_function(cstring) RETURNS box AS ... ;
964 CREATE FUNCTION my_box_out_function(box) RETURNS cstring AS ... ;
965
966 CREATE TYPE box (
967 INTERNALLENGTH = 16,
968 INPUT = my_box_in_function,
969 OUTPUT = my_box_out_function
970 );
971
972 CREATE TABLE myboxes (
973 id integer,
974 description box
975 );
976 </programlisting>
977 </para>
978
979 <para>
980 If the internal structure of <type>box</type> were an array of four
981 <type>float4</type> elements, we might instead use:
982 <programlisting>
983 CREATE TYPE box (
984 INTERNALLENGTH = 16,
985 INPUT = my_box_in_function,
986 OUTPUT = my_box_out_function,
987 ELEMENT = float4
988 );
989 </programlisting>
990 which would allow a box value's component numbers to be accessed
991 by subscripting. Otherwise the type behaves the same as before.
992 </para>
993
994 <para>
995 This example creates a large object type and uses it in
996 a table definition:
997 <programlisting>
998 CREATE TYPE bigobj (
999 INPUT = lo_filein, OUTPUT = lo_fileout,
1000 INTERNALLENGTH = VARIABLE
1001 );
1002 CREATE TABLE big_objs (
1003 id integer,
1004 obj bigobj
1005 );
1006 </programlisting>
1007 </para>
1008
1009 <para>
1010 More examples, including suitable input and output functions, are
1011 in <xref linkend="xtypes"/>.
1012 </para>
1013 </refsect1>
1014
1015 <refsect1 id="sql-createtype-compatibility">
1016 <title>Compatibility</title>
1017
1018 <para>
1019 The first form of the <command>CREATE TYPE</command> command, which
1020 creates a composite type, conforms to the <acronym>SQL</acronym> standard.
1021 The other forms are <productname>PostgreSQL</productname>
1022 extensions. The <command>CREATE TYPE</command> statement in
1023 the <acronym>SQL</acronym> standard also defines other forms that are not
1024 implemented in <productname>PostgreSQL</productname>.
1025 </para>
1026
1027 <para>
1028 The ability to create a composite type with zero attributes is
1029 a <productname>PostgreSQL</productname>-specific deviation from the
1030 standard (analogous to the same case in <command>CREATE TABLE</command>).
1031 </para>
1032 </refsect1>
1033
1034 <refsect1 id="sql-createtype-see-also">
1035 <title>See Also</title>
1036
1037 <simplelist type="inline">
1038 <member><xref linkend="sql-altertype"/></member>
1039 <member><xref linkend="sql-createdomain"/></member>
1040 <member><xref linkend="sql-createfunction"/></member>
1041 <member><xref linkend="sql-droptype"/></member>
1042 </simplelist>
1043 </refsect1>
1044
1045 </refentry>