CREATE TYPE

Function

CREATE TYPE creates a user-defined type (UDT). It encapsulates a group of related fields or values into a new and reusable data type. It is similar to the structure in programming languages, making the database table design more suitable for business logic, reducing redundancy, and improving readability. This function applies only to row-store tables.

There are composite types, base types, shell types, and enumerated types.

• Composite types

  A composite type is defined by a list of attribute names and their data types. If the data type of an attribute is collatable, you can specify a collation for the attribute. A composite type is essentially the same as the row type of a table. However, if you only need to define a structured data type, using CREATE TYPE to avoid creating an actual table.

  An independent composite type is useful as the parameter or return type of a user-defined function. For example, a user table needs to store the home and work addresses (including the province, city, detailed address, and postal code). The address structure needs to be reused to avoid redundant table fields. In this case, you can create a composite type.

  When creating a composite type, you must have the USAGE permission on all data types of its attributes.

• Base types

  To create a new base type (non-composite type or non-enumerated type), you need to define the input and output functions to encode and decode the type. This type is available for special service types (such as mobile number, ID card number, and amount with currency). The custom functions used by the base type must be coded in C or another low-level language.

  For example, the user contact information must be an 11-digit mobile number, non-digit characters are automatically filtered out to avoid invalid format recording.

• Shell types

  With this parameter, you can create a shell type, which is a type name without a definition. To create a shell type, use CREATE TYPE with only the type name. Shell types are needed as forward references when base types are created.

• Enumerated types

  An enumerated type is a list of enumerated values. Each value is a non-empty string with the maximum length of 64 bytes. Enumerated types are used to restrict field values to a fixed enumerated value set. They can replace the check constraints of other data types such as VARCHAR, improving data consistency and readability. For example, spelling errors can be avoided.

  For example, the order status can only be to be paid, paid, shipped, delivered, or cancelled. Ensure that the status values in all tables (order table and refund table) are the same to avoid spelling errors.

Precautions

• This syntax applies only to row-store tables.
• If a schema name is given, the type will be created in the specified schema. Otherwise, it will be created in the current schema. The type name must be distinct from the name of any existing type or domain in the same schema. (Because tables have associated data types, the type name must also be distinct from the name of any existing table in the same schema.)
• When creating a base type, you can place parameters in any order. The input_function and output_function parameters are mandatory, and other parameters are optional.
• You need to first create input and output functions and then create a new type. Input and output functions can be declared as results or parameters of the new type. The new type should first be defined as a shell type, which is a placeholder type that has no attributes except for the name and owner. This can be done by running CREATE TYPE name without extra parameters. Then, the I/O functions written in C can be defined to reference this shell type. Finally, CREATE TYPE with a full definition replaces the shell type with a complete, valid type definition. After that, the new type can be used normally.

Syntax

Composite type

1
2

CREATE TYPE name AS
    ( [ attribute_name data_type [ COLLATE collation ] [, ... ] ] )

Base type

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

CREATE TYPE name (
    INPUT = input_function,
    OUTPUT = output_function
    [ , RECEIVE = receive_function ]
    [ , SEND = send_function ]
    [ , TYPMOD_IN = type_modifier_input_function ]
    [ , TYPMOD_OUT = type_modifier_output_function ]
    [ , ANALYZE = analyze_function ]
    [ , INTERNALLENGTH = { internallength | VARIABLE } ]
    [ , PASSEDBYVALUE ]
    [ , ALIGNMENT = alignment ]
    [ , STORAGE = storage ]
    [ , LIKE = like_type ]
    [ , CATEGORY = category ]
    [ , PREFERRED = preferred ]
    [ , DEFAULT = default ]
    [ , ELEMENT = element ]
    [ , DELIMITER = delimiter ]
    [ , COLLATABLE = collatable ]
)

Shell type

1

CREATE TYPE name

Enumerated type

1
2

CREATE TYPE name AS ENUM
    ( [ 'label' [, ... ] ] )

Parameter Description

Example: Creating a Composite Type to Represent Address Information

1. Creates a composite type to represent address information, including the province, city, street, and house number.

   1 2 3 4 5 6

   CREATE TYPE address_type AS ( province VARCHAR(20), city VARCHAR(50), street VARCHAR(100), number VARCHAR(10) );

2. Use this type to create a table.

   1 2 3 4 5 6

   CREATE TABLE customers ( customer_id SERIAL PRIMARY KEY, name VARCHAR(100), billing_address address_type, shipping_address address_type );

3. Insert data.

   1 2 3 4 5 6

   INSERT INTO customers (name, billing_address, shipping_address) VALUES ( 'John Doe', ('SDong', 'QD', '123 Main St', '10001')::address_type, ('SDong', 'QD', '456 Park Ave', '10022')::address_type );

4. Query data.

   1 2 3 4 5

   SELECT name, (billing_address).street as bill_street, (shipping_address).city as ship_city FROM customers;

   

5. Update data.

   1 2 3

   UPDATE customers SET billing_address = ('SDong', 'QD','789 Broadway', '10003')::address_type WHERE customer_id = 1;

Example: Creating an Enumeration Type for E-commerce Order Management

1. Create an enumeration type. The enumeration values include pending, paid, shipped, delivered, and cancelled.

   1 2 3

   CREATE TYPE order_status AS ENUM ( 'pending', 'paid', 'shipped', 'delivered', 'cancelled' );

2. Create an order table. The status column uses the enumerated values of order_status.

   1 2 3 4 5 6 7 8 9

   CREATE TABLE orders ( order_id varchar(32) PRIMARY KEY, user_id int, goods_id int, amount decimal(10,2), status order_status DEFAULT 'pending', --The default status is pending. create_time timestamp DEFAULT now(), update_time timestamp DEFAULT now() );

3. Insert test data.

   1 2	INSERT INTO orders (order_id, user_id, goods_id, amount) VALUES ('ORD20260127001', 1001, 5001, 299.99); --The default status is pending.

4. Update the order status. Only the enumerated values can be updated.

   1	UPDATE orders SET status = 'paid', update_time = now() WHERE order_id = 'ORD20260127001';

5. Query data.

   1	SELECT * FROM orders WHERE order_id = 'ORD20260127001';

   

6. Check that the system reports an error when there is any non-enumerated value to ensure data consistency.

   1	UPDATE orders SET status = 'wait_pay' WHERE order_id = 'ORD20260127001';

   

Example: Creating a Basic Type for C Language Function Invocation

1. Before creating a base type, create a shell type (placeholder) so that the type can be referenced when I/O functions are defined.

   1	CREATE TYPE complex;

   This statement creates a placeholder for the type to be created, which can then be referenced when defining its I/O functions.

2. Define I/O functions. These functions reference the preceding shell type complex. When creating a function, you must declare the function in NOT FENCED mode. That is, the function must be executed in the CN or DN process to reduce the fork process and communication overhead. For more information, see CREATE FUNCTION.

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

   -- Create the text input function. CREATE FUNCTION complex_in(cstring) RETURNS complex AS 'filename' -- Replace it with the actual .so file path, for example, /opt/dws/complex.so. LANGUAGE C IMMUTABLE STRICT not fenced; -- Create the text output function. CREATE FUNCTION complex_out(complex) RETURNS cstring AS 'filename' -- Replace it with the actual .so file path. LANGUAGE C IMMUTABLE STRICT not fenced; -- Create the binary input function. CREATE FUNCTION complex_recv(internal) RETURNS complex AS 'filename' -- Replace it with the actual .so file path. LANGUAGE C IMMUTABLE STRICT not fenced; -- Create the binary output function. CREATE FUNCTION complex_send(complex) RETURNS bytea AS 'filename' -- Replace it with the actual .so file path. LANGUAGE C IMMUTABLE STRICT not fenced;

3. Create a complete base type to replace the previous shell type.

   1 2 3 4 5 6 7 8

   CREATE TYPE complex ( internallength = 16, -- Define the fixed length 16 bytes, corresponding to two double-precision values (each 8 bytes). input = complex_in, -- Bind the text input function. output = complex_out, -- Bind the text output function. receive = complex_recv, -- Bind the binary input function. send = complex_send, -- Bind the binary output function. alignment = double -- Specifies an 8-byte alignment, as required for double-precision values. );

4. View the C language code of the input, output, receive, and send functions for further development reference.

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72

   -- Define a structure body Complex: typedef struct Complex { double x; double y; } Complex; -- Define an input function: PG_FUNCTION_INFO_V1(complex_in); Datum complex_in(PG_FUNCTION_ARGS) { char *str = PG_GETARG_CSTRING(0); double x, y; Complex *result; if (sscanf(str, " ( %lf , %lf )", &x, &y) != 2) ereport(ERROR, (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION), errmsg("invalid input syntax for complex: \"%s\"", str))); result = (Complex *) palloc(sizeof(Complex)); result->x = x; result->y = y; PG_RETURN_POINTER(result); } -- Define an output function: PG_FUNCTION_INFO_V1(complex_out); Datum complex_out(PG_FUNCTION_ARGS) { Complex *complex = (Complex *) PG_GETARG_POINTER(0); char *result; result = (char *) palloc(100); snprintf(result, 100, "(%g,%g)", complex->x, complex->y); PG_RETURN_CSTRING(result); } -- Define a receive function: PG_FUNCTION_INFO_V1(complex_recv); Datum complex_recv(PG_FUNCTION_ARGS) { StringInfo buf = (StringInfo) PG_GETARG_POINTER(0); Complex *result; result = (Complex *) palloc(sizeof(Complex)); result->x = pq_getmsgfloat8(buf); result->y = pq_getmsgfloat8(buf); PG_RETURN_POINTER(result); } -- Define a send function: PG_FUNCTION_INFO_V1(complex_send); Datum complex_send(PG_FUNCTION_ARGS) { Complex *complex = (Complex *) PG_GETARG_POINTER(0); StringInfoData buf; pq_begintypsend(&buf); pq_sendfloat8(&buf, complex->x); pq_sendfloat8(&buf, complex->y); PG_RETURN_BYTEA_P(pq_endtypsend(&buf)); }

Helpful Links

ALTER TYPE, DROP TYPE