1CREATE TYPE(7)                   SQL Commands                   CREATE TYPE(7)
2
3
4

NAME

6       CREATE TYPE - define a new data type
7
8

SYNOPSIS

10       CREATE TYPE name AS
11           ( attribute_name data_type [, ... ] )
12
13       CREATE TYPE name AS ENUM
14           ( 'label' [, ... ] )
15
16       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
36       CREATE TYPE name
37
38

DESCRIPTION

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
43       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
50   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
58   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
64   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
70       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
79       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
97       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
122       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
133       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
153       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
163       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
174       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
179       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
185       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
198       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
207       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
225       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
230       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
235       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
241   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
252       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

PARAMETERS

269       name   The name (optionally schema-qualified) of a type to be created.
270
271       attribute_name
272              The name of an attribute (column) for the composite type.
273
274       data_type
275              The name of an existing data type to become a column of the com‐
276              posite type.
277
278       label  A string literal representing the textual label associated  with
279              one value of an enum type.
280
281       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
285       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
289       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
293       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
297       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
301       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
305       analyze_function
306              The  name  of  a function that performs statistical analysis for
307              the data type.
308
309       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
314       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
319       storage
320              The storage strategy for the data type. If  specified,  must  be
321              plain, external, extended, or main; the default is plain.
322
323       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
330       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
337       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
343       default
344              The default value for the data type. If  this  is  omitted,  the
345              default is null.
346
347       element
348              The  type  being created is an array; this specifies the type of
349              the array elements.
350
351       delimiter
352              The delimiter character to be used between values in arrays made
353              of this type.
354

NOTES

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
364       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
374       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
380       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
391       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

EXAMPLES

400       This example creates a composite type and uses it in a function defini‐
401       tion:
402
403       CREATE TYPE compfoo AS (f1 int, f2 text);
404
405       CREATE FUNCTION getfoo() RETURNS SETOF compfoo AS $$
406           SELECT fooid, fooname FROM foo
407       $$ LANGUAGE SQL;
408
409
410       This  example creates an enumerated type and uses it in a table defini‐
411       tion:
412
413       CREATE TYPE bug_status AS ENUM ('new', 'open', 'closed');
414
415       CREATE TABLE bug (
416           id serial,
417           description text,
418           status bug_status
419       );
420
421
422       This example creates the base data type box and then uses the type in a
423       table definition:
424
425       CREATE TYPE box;
426
427       CREATE FUNCTION my_box_in_function(cstring) RETURNS box AS ... ;
428       CREATE FUNCTION my_box_out_function(box) RETURNS cstring AS ... ;
429
430       CREATE TYPE box (
431           INTERNALLENGTH = 16,
432           INPUT = my_box_in_function,
433           OUTPUT = my_box_out_function
434       );
435
436       CREATE TABLE myboxes (
437           id integer,
438           description box
439       );
440
441
442       If the internal structure of box were an array of four float4 elements,
443       we might instead use:
444
445       CREATE TYPE box (
446           INTERNALLENGTH = 16,
447           INPUT = my_box_in_function,
448           OUTPUT = my_box_out_function,
449           ELEMENT = float4
450       );
451
452       which would allow a box value's component numbers  to  be  accessed  by
453       subscripting. Otherwise the type behaves the same as before.
454
455       This example creates a large object type and uses it in a table defini‐
456       tion:
457
458       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
467
468       More examples, including suitable input and output functions, are in in
469       the documentation.
470

COMPATIBILITY

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

SEE ALSO

476       CREATE FUNCTION [create_function(7)], DROP TYPE  [drop_type(7)],  ALTER
477       TYPE [alter_type(7)], CREATE DOMAIN [create_domain(7)]
478
479
480
481SQL - Language Statements         2011-09-22                    CREATE TYPE(7)
Impressum