1 <?xml version="1.0" encoding="UTF-8" standalone="no"?>
2 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>CREATE TYPE</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="sql-createtrigger.html" title="CREATE TRIGGER" /><link rel="next" href="sql-createuser.html" title="CREATE USER" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">CREATE TYPE</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="sql-createtrigger.html" title="CREATE TRIGGER">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><th width="60%" align="center">SQL Commands</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="sql-createuser.html" title="CREATE USER">Next</a></td></tr></table><hr /></div><div class="refentry" id="SQL-CREATETYPE"><div class="titlepage"></div><a id="id-1.9.3.94.1" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle">CREATE TYPE</span></h2><p>CREATE TYPE — define a new data type</p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><pre class="synopsis">
3 CREATE TYPE <em class="replaceable"><code>name</code></em> AS
4 ( [ <em class="replaceable"><code>attribute_name</code></em> <em class="replaceable"><code>data_type</code></em> [ COLLATE <em class="replaceable"><code>collation</code></em> ] [, ... ] ] )
6 CREATE TYPE <em class="replaceable"><code>name</code></em> AS ENUM
7 ( [ '<em class="replaceable"><code>label</code></em>' [, ... ] ] )
9 CREATE TYPE <em class="replaceable"><code>name</code></em> AS RANGE (
10 SUBTYPE = <em class="replaceable"><code>subtype</code></em>
11 [ , SUBTYPE_OPCLASS = <em class="replaceable"><code>subtype_operator_class</code></em> ]
12 [ , COLLATION = <em class="replaceable"><code>collation</code></em> ]
13 [ , CANONICAL = <em class="replaceable"><code>canonical_function</code></em> ]
14 [ , SUBTYPE_DIFF = <em class="replaceable"><code>subtype_diff_function</code></em> ]
15 [ , MULTIRANGE_TYPE_NAME = <em class="replaceable"><code>multirange_type_name</code></em> ]
18 CREATE TYPE <em class="replaceable"><code>name</code></em> (
19 INPUT = <em class="replaceable"><code>input_function</code></em>,
20 OUTPUT = <em class="replaceable"><code>output_function</code></em>
21 [ , RECEIVE = <em class="replaceable"><code>receive_function</code></em> ]
22 [ , SEND = <em class="replaceable"><code>send_function</code></em> ]
23 [ , TYPMOD_IN = <em class="replaceable"><code>type_modifier_input_function</code></em> ]
24 [ , TYPMOD_OUT = <em class="replaceable"><code>type_modifier_output_function</code></em> ]
25 [ , ANALYZE = <em class="replaceable"><code>analyze_function</code></em> ]
26 [ , SUBSCRIPT = <em class="replaceable"><code>subscript_function</code></em> ]
27 [ , INTERNALLENGTH = { <em class="replaceable"><code>internallength</code></em> | VARIABLE } ]
29 [ , ALIGNMENT = <em class="replaceable"><code>alignment</code></em> ]
30 [ , STORAGE = <em class="replaceable"><code>storage</code></em> ]
31 [ , LIKE = <em class="replaceable"><code>like_type</code></em> ]
32 [ , CATEGORY = <em class="replaceable"><code>category</code></em> ]
33 [ , PREFERRED = <em class="replaceable"><code>preferred</code></em> ]
34 [ , DEFAULT = <em class="replaceable"><code>default</code></em> ]
35 [ , ELEMENT = <em class="replaceable"><code>element</code></em> ]
36 [ , DELIMITER = <em class="replaceable"><code>delimiter</code></em> ]
37 [ , COLLATABLE = <em class="replaceable"><code>collatable</code></em> ]
40 CREATE TYPE <em class="replaceable"><code>name</code></em>
41 </pre></div><div class="refsect1" id="id-1.9.3.94.5"><h2>Description</h2><p>
42 <code class="command">CREATE TYPE</code> registers a new data type for use in
43 the current database. The user who defines a type becomes its
46 If a schema name is given then the type is created in the specified
47 schema. Otherwise it is created in the current schema. The type
48 name must be distinct from the name of any existing type or domain
49 in the same schema. (Because tables have associated data types,
50 the type name must also be distinct from the name of any existing
51 table in the same schema.)
53 There are five forms of <code class="command">CREATE TYPE</code>, as shown in the
54 syntax synopsis above. They respectively create a <em class="firstterm">composite
55 type</em>, an <em class="firstterm">enum type</em>, a <em class="firstterm">range type</em>, a
56 <em class="firstterm">base type</em>, or a <em class="firstterm">shell type</em>. The first four
57 of these are discussed in turn below. A shell type is simply a placeholder
58 for a type to be defined later; it is created by issuing <code class="command">CREATE
59 TYPE</code> with no parameters except for the type name. Shell types
60 are needed as forward references when creating range types and base types,
61 as discussed in those sections.
62 </p><div class="refsect2" id="id-1.9.3.94.5.5"><h3>Composite Types</h3><p>
63 The first form of <code class="command">CREATE TYPE</code>
64 creates a composite type.
65 The composite type is specified by a list of attribute names and data types.
66 An attribute's collation can be specified too, if its data type is
67 collatable. A composite type is essentially the same as the row type
68 of a table, but using <code class="command">CREATE TYPE</code> avoids the need to
69 create an actual table when all that is wanted is to define a type.
70 A stand-alone composite type is useful, for example, as the argument or
71 return type of a function.
73 To be able to create a composite type, you must
74 have <code class="literal">USAGE</code> privilege on all attribute types.
75 </p></div><div class="refsect2" id="SQL-CREATETYPE-ENUM"><h3>Enumerated Types</h3><p>
76 The second form of <code class="command">CREATE TYPE</code> creates an enumerated
77 (enum) type, as described in <a class="xref" href="datatype-enum.html" title="8.7. Enumerated Types">Section 8.7</a>.
78 Enum types take a list of quoted labels, each of which
79 must be less than <code class="symbol">NAMEDATALEN</code> bytes long (64 bytes in a
80 standard <span class="productname">PostgreSQL</span> build). (It is possible to
81 create an enumerated type with zero labels, but such a type cannot be used
82 to hold values before at least one label is added using <a class="link" href="sql-altertype.html" title="ALTER TYPE"><code class="command">ALTER TYPE</code></a>.)
83 </p></div><div class="refsect2" id="SQL-CREATETYPE-RANGE"><h3>Range Types</h3><p>
84 The third form of <code class="command">CREATE TYPE</code> creates a new
85 range type, as described in <a class="xref" href="rangetypes.html" title="8.17. Range Types">Section 8.17</a>.
87 The range type's <em class="replaceable"><code>subtype</code></em> can
88 be any type with an associated b-tree operator class (to determine the
89 ordering of values for the range type). Normally the subtype's default
90 b-tree operator class is used to determine ordering; to use a non-default
91 operator class, specify its name with <em class="replaceable"><code>subtype_opclass</code></em>. If the subtype is
92 collatable, and you want to use a non-default collation in the range's
93 ordering, specify the desired collation with the <em class="replaceable"><code>collation</code></em> option.
95 The optional <em class="replaceable"><code>canonical</code></em>
96 function must take one argument of the range type being defined, and
97 return a value of the same type. This is used to convert range values
98 to a canonical form, when applicable. See <a class="xref" href="rangetypes.html#RANGETYPES-DEFINING" title="8.17.8. Defining New Range Types">Section 8.17.8</a> for more information. Creating a
99 <em class="replaceable"><code>canonical</code></em> function
100 is a bit tricky, since it must be defined before the range type can be
101 declared. To do this, you must first create a shell type, which is a
102 placeholder type that has no properties except a name and an
103 owner. This is done by issuing the command <code class="literal">CREATE TYPE
104 <em class="replaceable"><code>name</code></em></code>, with no additional parameters. Then
105 the function can be declared using the shell type as argument and result,
106 and finally the range type can be declared using the same name. This
107 automatically replaces the shell type entry with a valid range type.
109 The optional <em class="replaceable"><code>subtype_diff</code></em>
110 function must take two values of the
111 <em class="replaceable"><code>subtype</code></em> type as argument,
112 and return a <code class="type">double precision</code> value representing the
113 difference between the two given values. While this is optional,
114 providing it allows much greater efficiency of GiST indexes on columns of
115 the range type. See <a class="xref" href="rangetypes.html#RANGETYPES-DEFINING" title="8.17.8. Defining New Range Types">Section 8.17.8</a> for more
118 The optional <em class="replaceable"><code>multirange_type_name</code></em>
119 parameter specifies the name of the corresponding multirange type. If not
120 specified, this name is chosen automatically as follows.
121 If the range type name contains the substring <code class="literal">range</code>, then
122 the multirange type name is formed by replacement of the <code class="literal">range</code>
123 substring with <code class="literal">multirange</code> in the range
124 type name. Otherwise, the multirange type name is formed by appending a
125 <code class="literal">_multirange</code> suffix to the range type name.
126 </p></div><div class="refsect2" id="id-1.9.3.94.5.8"><h3>Base Types</h3><p>
127 The fourth form of <code class="command">CREATE TYPE</code> creates a new base type
128 (scalar type). To create a new base type, you must be a superuser.
129 (This restriction is made because an erroneous type definition could
130 confuse or even crash the server.)
132 The parameters can appear in any order, not only that
133 illustrated above, and most are optional. You must register
134 two or more functions (using <code class="command">CREATE FUNCTION</code>) before
135 defining the type. The support functions
136 <em class="replaceable"><code>input_function</code></em> and
137 <em class="replaceable"><code>output_function</code></em>
138 are required, while the functions
139 <em class="replaceable"><code>receive_function</code></em>,
140 <em class="replaceable"><code>send_function</code></em>,
141 <em class="replaceable"><code>type_modifier_input_function</code></em>,
142 <em class="replaceable"><code>type_modifier_output_function</code></em>,
143 <em class="replaceable"><code>analyze_function</code></em>, and
144 <em class="replaceable"><code>subscript_function</code></em>
145 are optional. Generally these functions have to be coded in C
146 or another low-level language.
148 The <em class="replaceable"><code>input_function</code></em>
149 converts the type's external textual representation to the internal
150 representation used by the operators and functions defined for the type.
151 <em class="replaceable"><code>output_function</code></em>
152 performs the reverse transformation. The input function can be
153 declared as taking one argument of type <code class="type">cstring</code>,
154 or as taking three arguments of types
155 <code class="type">cstring</code>, <code class="type">oid</code>, <code class="type">integer</code>.
156 The first argument is the input text as a C string, the second
157 argument is the type's own OID (except for array types, which instead
158 receive their element type's OID),
159 and the third is the <code class="literal">typmod</code> of the destination column, if known
160 (-1 will be passed if not).
161 The input function must return a value of the data type itself.
162 Usually, an input function should be declared STRICT; if it is not,
163 it will be called with a NULL first parameter when reading a NULL
164 input value. The function must still return NULL in this case, unless
166 (This case is mainly meant to support domain input functions, which
167 might need to reject NULL inputs.)
168 The output function must be
169 declared as taking one argument of the new data type.
170 The output function must return type <code class="type">cstring</code>.
171 Output functions are not invoked for NULL values.
173 The optional <em class="replaceable"><code>receive_function</code></em>
174 converts the type's external binary representation to the internal
175 representation. If this function is not supplied, the type cannot
176 participate in binary input. The binary representation should be
177 chosen to be cheap to convert to internal form, while being reasonably
178 portable. (For example, the standard integer data types use network
179 byte order as the external binary representation, while the internal
180 representation is in the machine's native byte order.) The receive
181 function should perform adequate checking to ensure that the value is
183 The receive function can be declared as taking one argument of type
184 <code class="type">internal</code>, or as taking three arguments of types
185 <code class="type">internal</code>, <code class="type">oid</code>, <code class="type">integer</code>.
186 The first argument is a pointer to a <code class="type">StringInfo</code> buffer
187 holding the received byte string; the optional arguments are the
188 same as for the text input function.
189 The receive function must return a value of the data type itself.
190 Usually, a receive function should be declared STRICT; if it is not,
191 it will be called with a NULL first parameter when reading a NULL
192 input value. The function must still return NULL in this case, unless
194 (This case is mainly meant to support domain receive functions, which
195 might need to reject NULL inputs.)
196 Similarly, the optional
197 <em class="replaceable"><code>send_function</code></em> converts
198 from the internal representation to the external binary representation.
199 If this function is not supplied, the type cannot participate in binary
200 output. The send function must be
201 declared as taking one argument of the new data type.
202 The send function must return type <code class="type">bytea</code>.
203 Send functions are not invoked for NULL values.
205 You should at this point be wondering how the input and output functions
206 can be declared to have results or arguments of the new type, when they
207 have to be created before the new type can be created. The answer is that
208 the type should first be defined as a <em class="firstterm">shell type</em>, which is a
209 placeholder type that has no properties except a name and an owner. This
210 is done by issuing the command <code class="literal">CREATE TYPE
211 <em class="replaceable"><code>name</code></em></code>, with no additional parameters. Then the
212 C I/O functions can be defined referencing the shell type. Finally,
213 <code class="command">CREATE TYPE</code> with a full definition replaces the shell entry
214 with a complete, valid type definition, after which the new type can be
218 <em class="replaceable"><code>type_modifier_input_function</code></em>
219 and <em class="replaceable"><code>type_modifier_output_function</code></em>
220 are needed if the type supports modifiers, that is optional constraints
221 attached to a type declaration, such as <code class="literal">char(5)</code> or
222 <code class="literal">numeric(30,2)</code>. <span class="productname">PostgreSQL</span> allows
223 user-defined types to take one or more simple constants or identifiers as
224 modifiers. However, this information must be capable of being packed into a
225 single non-negative integer value for storage in the system catalogs. The
226 <em class="replaceable"><code>type_modifier_input_function</code></em>
227 is passed the declared modifier(s) in the form of a <code class="type">cstring</code>
228 array. It must check the values for validity (throwing an error if they
229 are wrong), and if they are correct, return a single non-negative
230 <code class="type">integer</code> value that will be stored as the column <span class="quote">“<span class="quote">typmod</span>”</span>.
231 Type modifiers will be rejected if the type does not have a
232 <em class="replaceable"><code>type_modifier_input_function</code></em>.
233 The <em class="replaceable"><code>type_modifier_output_function</code></em>
234 converts the internal integer typmod value back to the correct form for
235 user display. It must return a <code class="type">cstring</code> value that is the exact
236 string to append to the type name; for example <code class="type">numeric</code>'s
237 function might return <code class="literal">(30,2)</code>.
238 It is allowed to omit the
239 <em class="replaceable"><code>type_modifier_output_function</code></em>,
240 in which case the default display format is just the stored typmod integer
241 value enclosed in parentheses.
243 The optional <em class="replaceable"><code>analyze_function</code></em>
244 performs type-specific statistics collection for columns of the data type.
245 By default, <code class="command">ANALYZE</code> will attempt to gather statistics using
246 the type's <span class="quote">“<span class="quote">equals</span>”</span> and <span class="quote">“<span class="quote">less-than</span>”</span> operators, if there
247 is a default b-tree operator class for the type. For non-scalar types
248 this behavior is likely to be unsuitable, so it can be overridden by
249 specifying a custom analysis function. The analysis function must be
250 declared to take a single argument of type <code class="type">internal</code>, and return
251 a <code class="type">boolean</code> result. The detailed API for analysis functions appears
252 in <code class="filename">src/include/commands/vacuum.h</code>.
254 The optional <em class="replaceable"><code>subscript_function</code></em>
255 allows the data type to be subscripted in SQL commands. Specifying this
256 function does not cause the type to be considered a <span class="quote">“<span class="quote">true</span>”</span>
257 array type; for example, it will not be a candidate for the result type
258 of <code class="literal">ARRAY[]</code> constructs. But if subscripting a value
259 of the type is a natural notation for extracting data from it, then
260 a <em class="replaceable"><code>subscript_function</code></em> can
261 be written to define what that means. The subscript function must be
262 declared to take a single argument of type <code class="type">internal</code>, and
263 return an <code class="type">internal</code> result, which is a pointer to a struct
264 of methods (functions) that implement subscripting.
265 The detailed API for subscript functions appears
266 in <code class="filename">src/include/nodes/subscripting.h</code>.
267 It may also be useful to read the array implementation
268 in <code class="filename">src/backend/utils/adt/arraysubs.c</code>,
270 in <code class="filename">contrib/hstore/hstore_subs.c</code>.
271 Additional information appears in
272 <a class="xref" href="sql-createtype.html#SQL-CREATETYPE-ARRAY" title="Array Types">Array Types</a> below.
274 While the details of the new type's internal representation are only
275 known to the I/O functions and other functions you create to work with
276 the type, there are several properties of the internal representation
277 that must be declared to <span class="productname">PostgreSQL</span>.
279 <em class="replaceable"><code>internallength</code></em>.
280 Base data types can be fixed-length, in which case
281 <em class="replaceable"><code>internallength</code></em> is a
282 positive integer, or variable-length, indicated by setting
283 <em class="replaceable"><code>internallength</code></em>
284 to <code class="literal">VARIABLE</code>. (Internally, this is represented
285 by setting <code class="literal">typlen</code> to -1.) The internal representation of all
286 variable-length types must start with a 4-byte integer giving the total
287 length of this value of the type. (Note that the length field is often
288 encoded, as described in <a class="xref" href="storage-toast.html" title="66.2. TOAST">Section 66.2</a>; it's unwise
289 to access it directly.)
291 The optional flag <code class="literal">PASSEDBYVALUE</code> indicates that
292 values of this data type are passed by value, rather than by
293 reference. Types passed by value must be fixed-length, and their internal
294 representation cannot be larger than the size of the <code class="type">Datum</code> type
295 (4 bytes on some machines, 8 bytes on others).
297 The <em class="replaceable"><code>alignment</code></em> parameter
298 specifies the storage alignment required for the data type. The
299 allowed values equate to alignment on 1, 2, 4, or 8 byte boundaries.
300 Note that variable-length types must have an alignment of at least
301 4, since they necessarily contain an <code class="type">int4</code> as their first component.
303 The <em class="replaceable"><code>storage</code></em> parameter
304 allows selection of storage strategies for variable-length data
305 types. (Only <code class="literal">plain</code> is allowed for fixed-length
306 types.) <code class="literal">plain</code> specifies that data of the type
307 will always be stored in-line and not compressed.
308 <code class="literal">extended</code> specifies that the system will first
309 try to compress a long data value, and will move the value out of
310 the main table row if it's still too long.
311 <code class="literal">external</code> allows the value to be moved out of the
312 main table, but the system will not try to compress it.
313 <code class="literal">main</code> allows compression, but discourages moving
314 the value out of the main table. (Data items with this storage
315 strategy might still be moved out of the main table if there is no
316 other way to make a row fit, but they will be kept in the main
317 table preferentially over <code class="literal">extended</code> and
318 <code class="literal">external</code> items.)
320 All <em class="replaceable"><code>storage</code></em> values other
321 than <code class="literal">plain</code> imply that the functions of the data type
322 can handle values that have been <em class="firstterm">toasted</em>, as described
323 in <a class="xref" href="storage-toast.html" title="66.2. TOAST">Section 66.2</a> and <a class="xref" href="xtypes.html#XTYPES-TOAST" title="36.13.1. TOAST Considerations">Section 36.13.1</a>.
324 The specific other value given merely determines the default TOAST
325 storage strategy for columns of a toastable data type; users can pick
326 other strategies for individual columns using <code class="literal">ALTER TABLE
329 The <em class="replaceable"><code>like_type</code></em> parameter
330 provides an alternative method for specifying the basic representation
331 properties of a data type: copy them from some existing type. The values of
332 <em class="replaceable"><code>internallength</code></em>,
333 <em class="replaceable"><code>passedbyvalue</code></em>,
334 <em class="replaceable"><code>alignment</code></em>, and
335 <em class="replaceable"><code>storage</code></em> are copied from the
336 named type. (It is possible, though usually undesirable, to override
337 some of these values by specifying them along with the <code class="literal">LIKE</code>
338 clause.) Specifying representation this way is especially useful when
339 the low-level implementation of the new type <span class="quote">“<span class="quote">piggybacks</span>”</span> on an
340 existing type in some fashion.
342 The <em class="replaceable"><code>category</code></em> and
343 <em class="replaceable"><code>preferred</code></em> parameters can be
344 used to help control which implicit cast will be applied in ambiguous
345 situations. Each data type belongs to a category named by a single ASCII
346 character, and each type is either <span class="quote">“<span class="quote">preferred</span>”</span> or not within its
347 category. The parser will prefer casting to preferred types (but only from
348 other types within the same category) when this rule is helpful in
349 resolving overloaded functions or operators. For more details see <a class="xref" href="typeconv.html" title="Chapter 10. Type Conversion">Chapter 10</a>. For types that have no implicit casts to or from any
350 other types, it is sufficient to leave these settings at the defaults.
351 However, for a group of related types that have implicit casts, it is often
352 helpful to mark them all as belonging to a category and select one or two
353 of the <span class="quote">“<span class="quote">most general</span>”</span> types as being preferred within the category.
354 The <em class="replaceable"><code>category</code></em> parameter is
355 especially useful when adding a user-defined type to an existing built-in
356 category, such as the numeric or string types. However, it is also
357 possible to create new entirely-user-defined type categories. Select any
358 ASCII character other than an upper-case letter to name such a category.
360 A default value can be specified, in case a user wants columns of the
361 data type to default to something other than the null value.
362 Specify the default with the <code class="literal">DEFAULT</code> key word.
363 (Such a default can be overridden by an explicit <code class="literal">DEFAULT</code>
364 clause attached to a particular column.)
366 To indicate that a type is a fixed-length array type,
367 specify the type of the array
368 elements using the <code class="literal">ELEMENT</code> key word. For example, to
369 define an array of 4-byte integers (<code class="type">int4</code>), specify
370 <code class="literal">ELEMENT = int4</code>. For more details,
371 see <a class="xref" href="sql-createtype.html#SQL-CREATETYPE-ARRAY" title="Array Types">Array Types</a> below.
373 To indicate the delimiter to be used between values in the external
374 representation of arrays of this type, <em class="replaceable"><code>delimiter</code></em> can be
375 set to a specific character. The default delimiter is the comma
376 (<code class="literal">,</code>). Note that the delimiter is associated
377 with the array element type, not the array type itself.
379 If the optional Boolean
380 parameter <em class="replaceable"><code>collatable</code></em>
381 is true, column definitions and expressions of the type may carry
382 collation information through use of
383 the <code class="literal">COLLATE</code> clause. It is up to the
384 implementations of the functions operating on the type to actually
385 make use of the collation information; this does not happen
386 automatically merely by marking the type collatable.
387 </p></div><div class="refsect2" id="SQL-CREATETYPE-ARRAY"><h3>Array Types</h3><p>
388 Whenever a user-defined type is created,
389 <span class="productname">PostgreSQL</span> automatically creates an
390 associated array type, whose name consists of the element type's
391 name prepended with an underscore, and truncated if necessary to keep
392 it less than <code class="symbol">NAMEDATALEN</code> bytes long. (If the name
393 so generated collides with an existing type name, the process is
394 repeated until a non-colliding name is found.)
395 This implicitly-created array type is variable length and uses the
396 built-in input and output functions <code class="literal">array_in</code> and
397 <code class="literal">array_out</code>. Furthermore, this type is what the system
398 uses for constructs such as <code class="literal">ARRAY[]</code> over the
399 user-defined type. The array type tracks any changes in its
400 element type's owner or schema, and is dropped if the element type is.
402 You might reasonably ask why there is an <code class="option">ELEMENT</code>
403 option, if the system makes the correct array type automatically.
404 The main case where it's useful to use <code class="option">ELEMENT</code> is when you are
405 making a fixed-length type that happens to be internally an array of a number of
406 identical things, and you want to allow these things to be accessed
407 directly by subscripting, in addition to whatever operations you plan
408 to provide for the type as a whole. For example, type <code class="type">point</code>
409 is represented as just two floating-point numbers, which can be accessed
410 using <code class="literal">point[0]</code> and <code class="literal">point[1]</code>.
412 this facility only works for fixed-length types whose internal form
413 is exactly a sequence of identical fixed-length fields.
414 For historical reasons (i.e., this is clearly wrong but it's far too
415 late to change it), subscripting of fixed-length array types starts from
416 zero, rather than from one as for variable-length arrays.
418 Specifying the <code class="option">SUBSCRIPT</code> option allows a data type to
419 be subscripted, even though the system does not otherwise regard it as
420 an array type. The behavior just described for fixed-length arrays is
421 actually implemented by the <code class="option">SUBSCRIPT</code> handler
422 function <code class="function">raw_array_subscript_handler</code>, which is
423 used automatically if you specify <code class="option">ELEMENT</code> for a
424 fixed-length type without also writing <code class="option">SUBSCRIPT</code>.
426 When specifying a custom <code class="option">SUBSCRIPT</code> function, it is
427 not necessary to specify <code class="option">ELEMENT</code> unless
428 the <code class="option">SUBSCRIPT</code> handler function needs to
429 consult <code class="structfield">typelem</code> to find out what to return.
430 Be aware that specifying <code class="option">ELEMENT</code> causes the system to
431 assume that the new type contains, or is somehow physically dependent on,
432 the element type; thus for example changing properties of the element
433 type won't be allowed if there are any columns of the dependent type.
434 </p></div></div><div class="refsect1" id="id-1.9.3.94.6"><h2>Parameters</h2><div class="variablelist"><dl class="variablelist"><dt><span class="term"><em class="replaceable"><code>name</code></em></span></dt><dd><p>
435 The name (optionally schema-qualified) of a type to be created.
436 </p></dd><dt><span class="term"><em class="replaceable"><code>attribute_name</code></em></span></dt><dd><p>
437 The name of an attribute (column) for the composite type.
438 </p></dd><dt><span class="term"><em class="replaceable"><code>data_type</code></em></span></dt><dd><p>
439 The name of an existing data type to become a column of the
441 </p></dd><dt><span class="term"><em class="replaceable"><code>collation</code></em></span></dt><dd><p>
442 The name of an existing collation to be associated with a column of
443 a composite type, or with a range type.
444 </p></dd><dt><span class="term"><em class="replaceable"><code>label</code></em></span></dt><dd><p>
445 A string literal representing the textual label associated with
446 one value of an enum type.
447 </p></dd><dt><span class="term"><em class="replaceable"><code>subtype</code></em></span></dt><dd><p>
448 The name of the element type that the range type will represent ranges
450 </p></dd><dt><span class="term"><em class="replaceable"><code>subtype_operator_class</code></em></span></dt><dd><p>
451 The name of a b-tree operator class for the subtype.
452 </p></dd><dt><span class="term"><em class="replaceable"><code>canonical_function</code></em></span></dt><dd><p>
453 The name of the canonicalization function for the range type.
454 </p></dd><dt><span class="term"><em class="replaceable"><code>subtype_diff_function</code></em></span></dt><dd><p>
455 The name of a difference function for the subtype.
456 </p></dd><dt><span class="term"><em class="replaceable"><code>multirange_type_name</code></em></span></dt><dd><p>
457 The name of the corresponding multirange type.
458 </p></dd><dt><span class="term"><em class="replaceable"><code>input_function</code></em></span></dt><dd><p>
459 The name of a function that converts data from the type's
460 external textual form to its internal form.
461 </p></dd><dt><span class="term"><em class="replaceable"><code>output_function</code></em></span></dt><dd><p>
462 The name of a function that converts data from the type's
463 internal form to its external textual form.
464 </p></dd><dt><span class="term"><em class="replaceable"><code>receive_function</code></em></span></dt><dd><p>
465 The name of a function that converts data from the type's
466 external binary form to its internal form.
467 </p></dd><dt><span class="term"><em class="replaceable"><code>send_function</code></em></span></dt><dd><p>
468 The name of a function that converts data from the type's
469 internal form to its external binary form.
470 </p></dd><dt><span class="term"><em class="replaceable"><code>type_modifier_input_function</code></em></span></dt><dd><p>
471 The name of a function that converts an array of modifier(s) for the type
473 </p></dd><dt><span class="term"><em class="replaceable"><code>type_modifier_output_function</code></em></span></dt><dd><p>
474 The name of a function that converts the internal form of the type's
475 modifier(s) to external textual form.
476 </p></dd><dt><span class="term"><em class="replaceable"><code>analyze_function</code></em></span></dt><dd><p>
477 The name of a function that performs statistical analysis for the
479 </p></dd><dt><span class="term"><em class="replaceable"><code>subscript_function</code></em></span></dt><dd><p>
480 The name of a function that defines what subscripting a value of the
482 </p></dd><dt><span class="term"><em class="replaceable"><code>internallength</code></em></span></dt><dd><p>
483 A numeric constant that specifies the length in bytes of the new
484 type's internal representation. The default assumption is that
485 it is variable-length.
486 </p></dd><dt><span class="term"><em class="replaceable"><code>alignment</code></em></span></dt><dd><p>
487 The storage alignment requirement of the data type. If specified,
488 it must be <code class="literal">char</code>, <code class="literal">int2</code>,
489 <code class="literal">int4</code>, or <code class="literal">double</code>; the
490 default is <code class="literal">int4</code>.
491 </p></dd><dt><span class="term"><em class="replaceable"><code>storage</code></em></span></dt><dd><p>
492 The storage strategy for the data type. If specified, must be
493 <code class="literal">plain</code>, <code class="literal">external</code>,
494 <code class="literal">extended</code>, or <code class="literal">main</code>; the
495 default is <code class="literal">plain</code>.
496 </p></dd><dt><span class="term"><em class="replaceable"><code>like_type</code></em></span></dt><dd><p>
497 The name of an existing data type that the new type will have the
498 same representation as. The values of
499 <em class="replaceable"><code>internallength</code></em>,
500 <em class="replaceable"><code>passedbyvalue</code></em>,
501 <em class="replaceable"><code>alignment</code></em>, and
502 <em class="replaceable"><code>storage</code></em>
503 are copied from that type, unless overridden by explicit
504 specification elsewhere in this <code class="command">CREATE TYPE</code> command.
505 </p></dd><dt><span class="term"><em class="replaceable"><code>category</code></em></span></dt><dd><p>
506 The category code (a single ASCII character) for this type.
507 The default is <code class="literal">'U'</code> for <span class="quote">“<span class="quote">user-defined type</span>”</span>.
508 Other standard category codes can be found in
509 <a class="xref" href="catalog-pg-type.html#CATALOG-TYPCATEGORY-TABLE" title="Table 52.65. typcategory Codes">Table 52.65</a>. You may also choose
510 other ASCII characters in order to create custom categories.
511 </p></dd><dt><span class="term"><em class="replaceable"><code>preferred</code></em></span></dt><dd><p>
512 True if this type is a preferred type within its type category,
513 else false. The default is false. Be very careful about creating
514 a new preferred type within an existing type category, as this
515 could cause surprising changes in behavior.
516 </p></dd><dt><span class="term"><em class="replaceable"><code>default</code></em></span></dt><dd><p>
517 The default value for the data type. If this is omitted, the
519 </p></dd><dt><span class="term"><em class="replaceable"><code>element</code></em></span></dt><dd><p>
520 The type being created is an array; this specifies the type of
522 </p></dd><dt><span class="term"><em class="replaceable"><code>delimiter</code></em></span></dt><dd><p>
523 The delimiter character to be used between values in arrays made
525 </p></dd><dt><span class="term"><em class="replaceable"><code>collatable</code></em></span></dt><dd><p>
526 True if this type's operations can use collation information.
527 The default is false.
528 </p></dd></dl></div></div><div class="refsect1" id="SQL-CREATETYPE-NOTES"><h2>Notes</h2><p>
529 Because there are no restrictions on use of a data type once it's been
530 created, creating a base type or range type is tantamount to granting
531 public execute permission on the functions mentioned in the type definition.
533 not an issue for the sorts of functions that are useful in a type
534 definition. But you might want to think twice before designing a type
535 in a way that would require <span class="quote">“<span class="quote">secret</span>”</span> information to be used
536 while converting it to or from external form.
538 Before <span class="productname">PostgreSQL</span> version 8.3, the name of
539 a generated array type was always exactly the element type's name with one
540 underscore character (<code class="literal">_</code>) prepended. (Type names were
541 therefore restricted in length to one fewer character than other names.)
542 While this is still usually the case, the array type name may vary from
543 this in case of maximum-length names or collisions with user type names
544 that begin with underscore. Writing code that depends on this convention
545 is therefore deprecated. Instead, use
546 <code class="structname">pg_type</code>.<code class="structfield">typarray</code> to locate the array type
547 associated with a given type.
549 It may be advisable to avoid using type and table names that begin with
550 underscore. While the server will change generated array type names to
551 avoid collisions with user-given names, there is still risk of confusion,
552 particularly with old client software that may assume that type names
553 beginning with underscores always represent arrays.
555 Before <span class="productname">PostgreSQL</span> version 8.2, the shell-type
557 <code class="literal">CREATE TYPE <em class="replaceable"><code>name</code></em></code> did not exist.
558 The way to create a new base type was to create its input function first.
559 In this approach, <span class="productname">PostgreSQL</span> will first see
560 the name of the new data type as the return type of the input function.
561 The shell type is implicitly created in this situation, and then it
562 can be referenced in the definitions of the remaining I/O functions.
563 This approach still works, but is deprecated and might be disallowed in
564 some future release. Also, to avoid accidentally cluttering
565 the catalogs with shell types as a result of simple typos in function
566 definitions, a shell type will only be made this way when the input
567 function is written in C.
569 In <span class="productname">PostgreSQL</span> version 16 and later,
570 it is desirable for base types' input functions to
571 return <span class="quote">“<span class="quote">soft</span>”</span> errors using the
572 new <code class="function">errsave()</code>/<code class="function">ereturn()</code>
573 mechanism, rather than throwing <code class="function">ereport()</code>
574 exceptions as in previous versions.
575 See <code class="filename">src/backend/utils/fmgr/README</code> for more
577 </p></div><div class="refsect1" id="id-1.9.3.94.8"><h2>Examples</h2><p>
578 This example creates a composite type and uses it in
579 a function definition:
580 </p><pre class="programlisting">
581 CREATE TYPE compfoo AS (f1 int, f2 text);
583 CREATE FUNCTION getfoo() RETURNS SETOF compfoo AS $$
584 SELECT fooid, fooname FROM foo
588 This example creates an enumerated type and uses it in
590 </p><pre class="programlisting">
591 CREATE TYPE bug_status AS ENUM ('new', 'open', 'closed');
600 This example creates a range type:
601 </p><pre class="programlisting">
602 CREATE TYPE float8_range AS RANGE (subtype = float8, subtype_diff = float8mi);
605 This example creates the base data type <code class="type">box</code> and then uses the
606 type in a table definition:
607 </p><pre class="programlisting">
610 CREATE FUNCTION my_box_in_function(cstring) RETURNS box AS ... ;
611 CREATE FUNCTION my_box_out_function(box) RETURNS cstring AS ... ;
615 INPUT = my_box_in_function,
616 OUTPUT = my_box_out_function
619 CREATE TABLE myboxes (
625 If the internal structure of <code class="type">box</code> were an array of four
626 <code class="type">float4</code> elements, we might instead use:
627 </p><pre class="programlisting">
630 INPUT = my_box_in_function,
631 OUTPUT = my_box_out_function,
635 which would allow a box value's component numbers to be accessed
636 by subscripting. Otherwise the type behaves the same as before.
638 This example creates a large object type and uses it in
640 </p><pre class="programlisting">
642 INPUT = lo_filein, OUTPUT = lo_fileout,
643 INTERNALLENGTH = VARIABLE
645 CREATE TABLE big_objs (
651 More examples, including suitable input and output functions, are
652 in <a class="xref" href="xtypes.html" title="36.13. User-Defined Types">Section 36.13</a>.
653 </p></div><div class="refsect1" id="SQL-CREATETYPE-COMPATIBILITY"><h2>Compatibility</h2><p>
654 The first form of the <code class="command">CREATE TYPE</code> command, which
655 creates a composite type, conforms to the <acronym class="acronym">SQL</acronym> standard.
656 The other forms are <span class="productname">PostgreSQL</span>
657 extensions. The <code class="command">CREATE TYPE</code> statement in
658 the <acronym class="acronym">SQL</acronym> standard also defines other forms that are not
659 implemented in <span class="productname">PostgreSQL</span>.
661 The ability to create a composite type with zero attributes is
662 a <span class="productname">PostgreSQL</span>-specific deviation from the
663 standard (analogous to the same case in <code class="command">CREATE TABLE</code>).
664 </p></div><div class="refsect1" id="SQL-CREATETYPE-SEE-ALSO"><h2>See Also</h2><span class="simplelist"><a class="xref" href="sql-altertype.html" title="ALTER TYPE"><span class="refentrytitle">ALTER TYPE</span></a>, <a class="xref" href="sql-createdomain.html" title="CREATE DOMAIN"><span class="refentrytitle">CREATE DOMAIN</span></a>, <a class="xref" href="sql-createfunction.html" title="CREATE FUNCTION"><span class="refentrytitle">CREATE FUNCTION</span></a>, <a class="xref" href="sql-droptype.html" title="DROP TYPE"><span class="refentrytitle">DROP TYPE</span></a></span></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="sql-createtrigger.html" title="CREATE TRIGGER">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sql-createuser.html" title="CREATE USER">Next</a></td></tr><tr><td width="40%" align="left" valign="top">CREATE TRIGGER </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="40%" align="right" valign="top"> CREATE USER</td></tr></table></div></body></html>
begriffs.com / projects / ai-pg / blob