1CREATE TYPE(7) SQL Commands CREATE TYPE(7)
2
6 CREATE TYPE - define a new data type
7
10 CREATE TYPE name AS
11 ( attribute_name data_type [, ... ] )
12 CREATE TYPE name AS ENUM
14 ( 'label' [, ... ] )
15 CREATE TYPE name (
17 INPUT = input_function,
18 OUTPUT = output_function
19 [ , RECEIVE = receive_function ]
20 [ , SEND = send_function ]
21 [ , TYPMOD_IN = type_modifier_input_function ]
22 [ , TYPMOD_OUT = type_modifier_output_function ]
23 [ , ANALYZE = analyze_function ]
24 [ , INTERNALLENGTH = { internallength | VARIABLE } ]
25 [ , PASSEDBYVALUE ]
26 [ , ALIGNMENT = alignment ]
27 [ , STORAGE = storage ]
28 [ , LIKE = like_type ]
29 [ , CATEGORY = category ]
30 [ , PREFERRED = preferred ]
31 [ , DEFAULT = default ]
32 [ , ELEMENT = element ]
33 [ , DELIMITER = delimiter ]
34 )
35 CREATE TYPE name
37
40 CREATE TYPE registers a new data type for use in the current database.
41 The user who defines a type becomes its owner.
42 If a schema name is given then the type is created in the specified
44 schema. Otherwise it is created in the current schema. The type name
45 must be distinct from the name of any existing type or domain in the
46 same schema. (Because tables have associated data types, the type name
47 must also be distinct from the name of any existing table in the same
48 schema.)
49 COMPOSITE TYPES
51 The first form of CREATE TYPE creates a composite type. The composite
52 type is specified by a list of attribute names and data types. This is
53 essentially the same as the row type of a table, but using CREATE TYPE
54 avoids the need to create an actual table when all that is wanted is to
55 define a type. A stand-alone composite type is useful as the argument
56 or return type of a function.
57 ENUMERATED TYPES
59 The second form of CREATE TYPE creates an enumerated (enum) type, as
60 described in in the documentation. Enum types take a list of one or
61 more quoted labels, each of which must be less than NAMEDATALEN bytes
62 long (64 in a standard PostgreSQL build).
63 BASE TYPES
65 The third form of CREATE TYPE creates a new base type (scalar type). To
66 create a new base type, you must be a superuser. (This restriction is
67 made because an erroneous type definition could confuse or even crash
68 the server.)
69 The parameters can appear in any order, not only that illustrated
71 above, and most are optional. You must register two or more functions
72 (using CREATE FUNCTION) before defining the type. The support functions
73 input_function and output_function are required, while the functions
74 receive_function, send_function, type_modifier_input_function,
75 type_modifier_output_function and analyze_function are optional. Gener‐
76 ally these functions have to be coded in C or another low-level lan‐
77 guage.
78 The input_function converts the type's external textual representation
80 to the internal representation used by the operators and functions
81 defined for the type. output_function performs the reverse transforma‐
82 tion. The input function can be declared as taking one argument of type
83 cstring, or as taking three arguments of types cstring, oid, integer.
84 The first argument is the input text as a C string, the second argument
85 is the type's own OID (except for array types, which instead receive
86 their element type's OID), and the third is the typmod of the destina‐
87 tion column, if known (-1 will be passed if not). The input function
88 must return a value of the data type itself. Usually, an input func‐
89 tion should be declared STRICT; if it is not, it will be called with a
90 NULL first parameter when reading a NULL input value. The function must
91 still return NULL in this case, unless it raises an error. (This case
92 is mainly meant to support domain input functions, which might need to
93 reject NULL inputs.) The output function must be declared as taking
94 one argument of the new data type. The output function must return
95 type cstring. Output functions are not invoked for NULL values.
96 The optional receive_function converts the type's external binary rep‐
98 resentation to the internal representation. If this function is not
99 supplied, the type cannot participate in binary input. The binary rep‐
100 resentation should be chosen to be cheap to convert to internal form,
101 while being reasonably portable. (For example, the standard integer
102 data types use network byte order as the external binary representa‐
103 tion, while the internal representation is in the machine's native byte
104 order.) The receive function should perform adequate checking to ensure
105 that the value is valid. The receive function can be declared as tak‐
106 ing one argument of type internal, or as taking three arguments of
107 types internal, oid, integer. The first argument is a pointer to a
108 StringInfo buffer holding the received byte string; the optional argu‐
109 ments are the same as for the text input function. The receive func‐
110 tion must return a value of the data type itself. Usually, a receive
111 function should be declared STRICT; if it is not, it will be called
112 with a NULL first parameter when reading a NULL input value. The func‐
113 tion must still return NULL in this case, unless it raises an error.
114 (This case is mainly meant to support domain receive functions, which
115 might need to reject NULL inputs.) Similarly, the optional send_func‐
116 tion converts from the internal representation to the external binary
117 representation. If this function is not supplied, the type cannot par‐
118 ticipate in binary output. The send function must be declared as taking
119 one argument of the new data type. The send function must return type
120 bytea. Send functions are not invoked for NULL values.
121 You should at this point be wondering how the input and output func‐
123 tions can be declared to have results or arguments of the new type,
124 when they have to be created before the new type can be created. The
125 answer is that the type should first be defined as a shell type, which
126 is a placeholder type that has no properties except a name and an
127 owner. This is done by issuing the command CREATE TYPE name, with no
128 additional parameters. Then the I/O functions can be defined referenc‐
129 ing the shell type. Finally, CREATE TYPE with a full definition
130 replaces the shell entry with a complete, valid type definition, after
131 which the new type can be used normally.
132 The optional type_modifier_input_function and type_modifier_out‐
134 put_function are needed if the type supports modifiers, that is
135 optional constraints attached to a type declaration, such as char(5) or
136 numeric(30,2). PostgreSQL allows user-defined types to take one or more
137 simple constants or identifiers as modifiers. However, this information
138 must be capable of being packed into a single non-negative integer
139 value for storage in the system catalogs. The type_modifier_input_func‐
140 tion is passed the declared modifier(s) in the form of a cstring array.
141 It must check the values for validity (throwing an error if they are
142 wrong), and if they are correct, return a single non-negative integer
143 value that will be stored as the column ``typmod''. Type modifiers
144 will be rejected if the type does not have a type_modifier_input_func‐
145 tion. The type_modifier_output_function converts the internal integer
146 typmod value back to the correct form for user display. It must return
147 a cstring value that is the exact string to append to the type name;
148 for example numeric's function might return (30,2). It is allowed to
149 omit the type_modifier_output_function, in which case the default dis‐
150 play format is just the stored typmod integer value enclosed in paren‐
151 theses.
152 The optional analyze_function performs type-specific statistics collec‐
154 tion for columns of the data type. By default, ANALYZE will attempt to
155 gather statistics using the type's ``equals'' and ``less-than'' opera‐
156 tors, if there is a default b-tree operator class for the type. For
157 non-scalar types this behavior is likely to be unsuitable, so it can be
158 overridden by specifying a custom analysis function. The analysis func‐
159 tion must be declared to take a single argument of type internal, and
160 return a boolean result. The detailed API for analysis functions
161 appears in src/include/commands/vacuum.h.
162 While the details of the new type's internal representation are only
164 known to the I/O functions and other functions you create to work with
165 the type, there are several properties of the internal representation
166 that must be declared to PostgreSQL. Foremost of these is internal‐
167 length. Base data types can be fixed-length, in which case internal‐
168 length is a positive integer, or variable length, indicated by setting
169 internallength to VARIABLE. (Internally, this is represented by setting
170 typlen to -1.) The internal representation of all variable-length types
171 must start with a 4-byte integer giving the total length of this value
172 of the type.
173 The optional flag PASSEDBYVALUE indicates that values of this data type
175 are passed by value, rather than by reference. You cannot pass by value
176 types whose internal representation is larger than the size of the
177 Datum type (4 bytes on most machines, 8 bytes on a few).
178 The alignment parameter specifies the storage alignment required for
180 the data type. The allowed values equate to alignment on 1, 2, 4, or 8
181 byte boundaries. Note that variable-length types must have an align‐
182 ment of at least 4, since they necessarily contain an int4 as their
183 first component.
184 The storage parameter allows selection of storage strategies for vari‐
186 able-length data types. (Only plain is allowed for fixed-length types.)
187 plain specifies that data of the type will always be stored in-line and
188 not compressed. extended specifies that the system will first try to
189 compress a long data value, and will move the value out of the main ta‐
190 ble row if it's still too long. external allows the value to be moved
191 out of the main table, but the system will not try to compress it.
192 main allows compression, but discourages moving the value out of the
193 main table. (Data items with this storage strategy might still be moved
194 out of the main table if there is no other way to make a row fit, but
195 they will be kept in the main table preferentially over extended and
196 external items.)
197 The like_type parameter provides an alternative method for specifying
199 the basic representation properties of a data type: copy them from some
200 existing type. The values of internallength, passedbyvalue, alignment,
201 and storage are copied from the named type. (It is possible, though
202 usually undesirable, to override some of these values by specifying
203 them along with the LIKE clause.) Specifying representation this way is
204 especially useful when the low-level implementation of the new type
205 ``piggybacks'' on an existing type in some fashion.
206 The category and preferred parameters can be used to help control which
208 implicit cast will be applied in ambiguous situations. Each data type
209 belongs to a category named by a single ASCII character, and each type
210 is either ``preferred'' or not within its category. The parser will
211 prefer casting to preferred types (but only from other types within the
212 same category) when this rule is helpful in resolving overloaded func‐
213 tions or operators. For more details see in the documentation. For
214 types that have no implicit casts to or from any other types, it is
215 sufficient to leave these settings at the defaults. However, for a
216 group of related types that have implicit casts, it is often helpful to
217 mark them all as belonging to a category and select one or two of the
218 ``most general'' types as being preferred within the category. The
219 category parameter is especially useful when adding a user-defined type
220 to an existing built-in category, such as the numeric or string types.
221 However, it is also possible to create new entirely-user-defined type
222 categories. Select any ASCII character other than an upper-case letter
223 to name such a category.
224 A default value can be specified, in case a user wants columns of the
226 data type to default to something other than the null value. Specify
227 the default with the DEFAULT key word. (Such a default can be overrid‐
228 den by an explicit DEFAULT clause attached to a particular column.)
229 To indicate that a type is an array, specify the type of the array ele‐
231 ments using the ELEMENT key word. For example, to define an array of
232 4-byte integers (int4), specify ELEMENT = int4. More details about
233 array types appear below.
234 To indicate the delimiter to be used between values in the external
236 representation of arrays of this type, delimiter can be set to a spe‐
237 cific character. The default delimiter is the comma (,). Note that the
238 delimiter is associated with the array element type, not the array type
239 itself.
240 ARRAY TYPES
242 Whenever a user-defined type is created, PostgreSQL automatically cre‐
243 ates an associated array type, whose name consists of the base type's
244 name prepended with an underscore, and truncated if necessary to keep
245 it less than NAMEDATALEN bytes long. (If the name so generated collides
246 with an existing type name, the process is repeated until a non-collid‐
247 ing name is found.) This implicitly-created array type is variable
248 length and uses the built-in input and output functions array_in and
249 array_out. The array type tracks any changes in its element type's
250 owner or schema, and is dropped if the element type is.
251 You might reasonably ask why there is an ELEMENT option, if the system
253 makes the correct array type automatically. The only case where it's
254 useful to use ELEMENT is when you are making a fixed-length type that
255 happens to be internally an array of a number of identical things, and
256 you want to allow these things to be accessed directly by subscripting,
257 in addition to whatever operations you plan to provide for the type as
258 a whole. For example, type point is represented as just two floating-
259 point numbers, which it allows to be accessed as point[0] and point[1].
260 Note that this facility only works for fixed-length types whose inter‐
261 nal form is exactly a sequence of identical fixed-length fields. A sub‐
262 scriptable variable-length type must have the generalized internal rep‐
263 resentation used by array_in and array_out. For historical reasons
264 (i.e., this is clearly wrong but it's far too late to change it), sub‐
265 scripting of fixed-length array types starts from zero, rather than
266 from one as for variable-length arrays.
267
269 name The name (optionally schema-qualified) of a type to be created.
270 attribute_name
272 The name of an attribute (column) for the composite type.
273 data_type
275 The name of an existing data type to become a column of the com‐
276 posite type.
277 label A string literal representing the textual label associated with
279 one value of an enum type.
280 input_function
282 The name of a function that converts data from the type's exter‐
283 nal textual form to its internal form.
284 output_function
286 The name of a function that converts data from the type's inter‐
287 nal form to its external textual form.
288 receive_function
290 The name of a function that converts data from the type's exter‐
291 nal binary form to its internal form.
292 send_function
294 The name of a function that converts data from the type's inter‐
295 nal form to its external binary form.
296 type_modifier_input_function
298 The name of a function that converts an array of modifier(s) for
299 the type into internal form.
300 type_modifier_output_function
302 The name of a function that converts the internal form of the
303 type's modifier(s) to external textual form.
304 analyze_function
306 The name of a function that performs statistical analysis for
307 the data type.
308 internallength
310 A numeric constant that specifies the length in bytes of the new
311 type's internal representation. The default assumption is that
312 it is variable-length.
313 alignment
315 The storage alignment requirement of the data type. If speci‐
316 fied, it must be char, int2, int4, or double; the default is
317 int4.
318 storage
320 The storage strategy for the data type. If specified, must be
321 plain, external, extended, or main; the default is plain.
322 like_type
324 The name of an existing data type that the new type will have
325 the same representation as. The values of internallength,
326 passedbyvalue, alignment, and storage are copied from that type,
327 unless overridden by explicit specification elsewhere in this
328 CREATE TYPE command.
329 category
331 The category code (a single ASCII character) for this type. The
332 default is 'U' for ``user-defined type''. Other standard cate‐
333 gory codes can be found in in the documentation. You may also
334 choose other ASCII characters in order to create custom cate‐
335 gories.
336 preferred
338 True if this type is a preferred type within its type category,
339 else false. The default is false. Be very careful about creating
340 a new preferred type within an existing type category, as this
341 could cause surprising changes in behavior.
342 default
344 The default value for the data type. If this is omitted, the
345 default is null.
346 element
348 The type being created is an array; this specifies the type of
349 the array elements.
350 delimiter
352 The delimiter character to be used between values in arrays made
353 of this type.
354
356 Because there are no restrictions on use of a data type once it's been
357 created, creating a base type is tantamount to granting public execute
358 permission on the functions mentioned in the type definition. This is
359 usually not an issue for the sorts of functions that are useful in a
360 type definition. But you might want to think twice before designing a
361 type in a way that would require ``secret'' information to be used
362 while converting it to or from external form.
363 Before PostgreSQL version 8.3, the name of a generated array type was
365 always exactly the element type's name with one underscore character
366 (_) prepended. (Type names were therefore restricted in length to one
367 less character than other names.) While this is still usually the
368 case, the array type name may vary from this in case of maximum-length
369 names or collisions with user type names that begin with underscore.
370 Writing code that depends on this convention is therefore deprecated.
371 Instead, use pg_type.typarray to locate the array type associated with
372 a given type.
373 It may be advisable to avoid using type and table names that begin with
375 underscore. While the server will change generated array type names to
376 avoid collisions with user-given names, there is still risk of confu‐
377 sion, particularly with old client software that may assume that type
378 names beginning with underscores always represent arrays.
379 Before PostgreSQL version 8.2, the syntax CREATE TYPE name did not
381 exist. The way to create a new base type was to create its input func‐
382 tion first. In this approach, PostgreSQL will first see the name of
383 the new data type as the return type of the input function. The shell
384 type is implicitly created in this situation, and then it can be refer‐
385 enced in the definitions of the remaining I/O functions. This approach
386 still works, but is deprecated and might be disallowed in some future
387 release. Also, to avoid accidentally cluttering the catalogs with shell
388 types as a result of simple typos in function definitions, a shell type
389 will only be made this way when the input function is written in C.
390 In PostgreSQL versions before 7.3, it was customary to avoid creating a
392 shell type at all, by replacing the functions' forward references to
393 the type name with the placeholder pseudotype opaque. The cstring argu‐
394 ments and results also had to be declared as opaque before 7.3. To sup‐
395 port loading of old dump files, CREATE TYPE will accept I/O functions
396 declared using opaque, but it will issue a notice and change the func‐
397 tion declarations to use the correct types.
398
400 This example creates a composite type and uses it in a function defini‐
401 tion:
402 CREATE TYPE compfoo AS (f1 int, f2 text);
404 CREATE FUNCTION getfoo() RETURNS SETOF compfoo AS $$
406 SELECT fooid, fooname FROM foo
407 $$ LANGUAGE SQL;
408 This example creates an enumerated type and uses it in a table defini‐
411 tion:
412 CREATE TYPE bug_status AS ENUM ('new', 'open', 'closed');
414 CREATE TABLE bug (
416 id serial,
417 description text,
418 status bug_status
419 );
420 This example creates the base data type box and then uses the type in a
423 table definition:
424 CREATE TYPE box;
426 CREATE FUNCTION my_box_in_function(cstring) RETURNS box AS ... ;
428 CREATE FUNCTION my_box_out_function(box) RETURNS cstring AS ... ;
429 CREATE TYPE box (
431 INTERNALLENGTH = 16,
432 INPUT = my_box_in_function,
433 OUTPUT = my_box_out_function
434 );
435 CREATE TABLE myboxes (
437 id integer,
438 description box
439 );
440 If the internal structure of box were an array of four float4 elements,
443 we might instead use:
444 CREATE TYPE box (
446 INTERNALLENGTH = 16,
447 INPUT = my_box_in_function,
448 OUTPUT = my_box_out_function,
449 ELEMENT = float4
450 );
451 which would allow a box value's component numbers to be accessed by
453 subscripting. Otherwise the type behaves the same as before.
454 This example creates a large object type and uses it in a table defini‐
456 tion:
457 CREATE TYPE bigobj (
459 INPUT = lo_filein, OUTPUT = lo_fileout,
460 INTERNALLENGTH = VARIABLE
461 );
462 CREATE TABLE big_objs (
463 id integer,
464 obj bigobj
465 );
466 More examples, including suitable input and output functions, are in in
469 the documentation.
470
472 This CREATE TYPE command is a PostgreSQL extension. There is a CREATE
473 TYPE statement in the SQL standard that is rather different in detail.
474
476 CREATE FUNCTION [create_function(7)], DROP TYPE [drop_type(7)], ALTER
477 TYPE [alter_type(7)], CREATE DOMAIN [create_domain(7)]
478SQL - Language Statements 2011-09-22 CREATE TYPE(7)