Use CREATE TYPE to create custom data types - PolarDB

CREATE TYPE registers a new user-defined type in the current database. The user who runs the statement automatically becomes the owner of the new type.

If you specify a schema name, the type is created in that schema. Otherwise, it is created in the current schema.

A type name must be unique within its schema — distinct from any existing type, domain, or table name in the same schema. Because tables have associated data types, table names are also considered taken.

Syntax

CREATE TYPE has five variants. This topic covers the first four. The fifth — CREATE TYPE name with no additional parameters — creates a shell type, which is a placeholder used as a forward reference when defining range types and base types.

-- Composite type
CREATE [ OR REPLACE ] TYPE name AS
    ( [ attribute_name data_type [ COLLATE collation ] [, ... ] ] );

-- Enumerated type
CREATE TYPE name AS ENUM
    ( [ 'label' [, ... ] ] );

-- Range type
CREATE TYPE name AS RANGE (
    SUBTYPE = subtype
    [ , SUBTYPE_OPCLASS = subtype_operator_class ]
    [ , COLLATION = collation ]
    [ , CANONICAL = canonical_function ]
    [ , SUBTYPE_DIFF = subtype_diff_function ]
);

-- Base type
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 (forward reference placeholder)
CREATE TYPE name;

Type variants

Composite type

A composite type is a set of named attributes, each with a data type. It is similar to a table's row type, but creating a composite type does not create a table.

Use a composite type as a parameter or return type for functions when you need to pass or return multiple values as a single structured value.

OR REPLACE behavior

OR REPLACE replaces an existing composite type of the same name without requiring you to drop it first. This updates the type definition in place.

Proceed with caution when you use the OR REPLACE clause because the clause overwrites the definition of the original type. The overwrite operation may affect the database objects that depend on the type.

Permission requirement

To create a composite type, you must have the USAGE privilege on the data types of all its attributes.

Enumerated type

An enumerated type is an ordered set of labels enclosed in single quotes. Each label can be up to NAMEDATALEN bytes — typically 64 bytes.

An enumerated type can be created with no labels. The type cannot store any value until you add at least one label with ALTER TYPE.

Range type

A range type represents a continuous interval of values based on a subtype — the element data type the range is built on. The subtype must have a B-tree operator class that defines the ordering of values. By default, the subtype's default B-tree operator class is used. Specify SUBTYPE_OPCLASS to use a non-default class, and COLLATION to apply a non-default collation.

Canonical function

An optional canonical function converts range values to a canonical representation. Because this function must accept and return the range type before that type exists, you must first create a shell type as a forward reference:

Create the shell type: CREATE TYPE name;
Define the canonical function, referencing the shell type.
Create the full range type with CANONICAL = canonical_function.

PolarDB then replaces the shell type with the fully defined range type.

Subtype diff function

An optional subtype_diff function accepts two subtype values and returns a double precision value representing their difference. Providing this function improves the efficiency of GiST (Generalized Search Tree) indexes on range-type columns.

Base type

A base type (also called a scalar type) defines how PolarDB stores and processes a completely new kind of data. Creating a base type requires superuser privileges, because an improperly defined base type can cause unexpected server behavior.

Required functions

Before creating a base type, register its I/O functions with CREATE FUNCTION:

Function	Purpose	Required
`input_function`	Converts the external text representation to the internal representation	Yes
`output_function`	Converts the internal representation back to external text	Yes
`receive_function`	Converts the external binary representation to the internal representation	No
`send_function`	Converts the internal representation to the external binary representation	No
`type_modifier_input_function`	Validates and encodes type modifiers into a non-negative integer stored in `typmod`	No
`type_modifier_output_function`	Converts the stored `typmod` integer back to a readable string appended to the type name	No
`analyze_function`	Collects statistics on the type for the query planner	No

These functions typically must be written in C or another low-level language because they interact directly with the internal database storage layer.

Shell type forward reference

Because input_function and output_function must reference the new type as a parameter or return type before the type exists, first create a shell type:

CREATE TYPE name;
Write and register input_function and output_function in C, referencing the shell type.
CREATE TYPE name ( INPUT = ..., OUTPUT = ..., ... );

The full definition replaces the shell type, making the new type available for database operations.

I/O function signatures

input_function accepts one of these signatures:

(cstring) — a single C string parameter
(cstring, oid, integer) — the C string, the Object Identifier (OID) of the type (or the array element type), and the type modifier (typmod; -1 if unknown)

It must return the new base type. Declare it as STRICT so it is not called when the input is NULL.

output_function accepts the new type and returns cstring. It is not called when the value is NULL.

receive_function processes binary input. Without it, binary input is not supported. It accepts (internal) or (internal, oid, integer), where internal is a StringInfo buffer of received bytes. In most cases, the receive_function is also declared as STRICT. Otherwise, when the function receives the input value NULL, the parameter of the internal type is NULL, and the function returns NULL unless errors occur.

send_function produces binary output. Without it, binary output is not supported. It accepts the new type and returns bytea. The send_function is not called when the value is NULL.

Type modifiers

If the base type supports modifiers such as char(5) or numeric(30,2), define both type_modifier_input_function and type_modifier_output_function:

type_modifier_input_function receives declared modifiers as a cstring[] array, validates them, and returns a non-negative integer stored in typmod. It throws an error for invalid modifiers.
type_modifier_output_function converts typmod back to a cstring appended after the type name (for example, (30,2) for numeric). Omit this function if enclosing typmod in parentheses is acceptable as the default display.

If the base type has no constraints specified by the type_modifier_input_function, any type modifiers are denied.

Analyze function

By default, ANALYZE uses the equals and less-than operators of the type's default B-tree operator class to gather statistics. For non-scalar types, replace this with a custom analyze function. The function must accept a single internal parameter and return boolean.

Internal representation attributes

Parameter	Description	Default
`INTERNALLENGTH`	Length in bytes of the internal representation. Use `VARIABLE` for variable-length types (sets `typlen` to `-1`). Variable-length representations must begin with a 4-byte integer holding the total length.	Variable
`PASSEDBYVALUE`	Passes values by value rather than by reference. Only valid for fixed-length types no larger than the `Datum` type (4 or 8 bytes depending on the platform).	—
`ALIGNMENT`	Memory alignment boundary: `char` (1 byte), `int2` (2 bytes), `int4` (4 bytes), or `double` (8 bytes). Variable-length types require at least `int4` alignment because they store a 4-byte length header.	`int4`
`STORAGE`	Storage strategy for variable-length types. Fixed-length types support only `plain`. See the storage strategies table below.	`plain`
`LIKE`	Copies `internallength`, `passedbyvalue`, `alignment`, and `storage` from an existing type. Values specified elsewhere in the statement override the copied values.	—
`CATEGORY`	A single ASCII character identifying the type's category for implicit cast disambiguation. The default `U` means user-defined type. Non-uppercase ASCII characters are available for custom categories.	`U`
`PREFERRED`	Marks this type as the preferred implicit cast target within its category. Use caution when setting this for existing categories, as it can change query behavior.	`false`
`DEFAULT`	Default value for columns of this type. Column-level `DEFAULT` clauses override this.	Empty
`ELEMENT`	Element type of the array this type represents. Use this for fixed-length types that are internally arrays with subscript access (for example, `point` with `point[0]` and `point[1]`).	—
`DELIMITER`	Character used to separate values in the external text representation of arrays of this element type.	`,`
`COLLATABLE`	If `true`, columns and expressions of this type can carry collation information via `COLLATE`. Functions that use collation must explicitly query it; marking the type as collatable alone does not change function behavior.	`false`

Storage strategies

Strategy	Behavior
`plain`	Always stored inline in the row; no compression. The only option for fixed-length types.
`extended`	Compresses long values; moves out of the row if needed.
`external`	Moves out of the row if needed; no compression.
`main`	Compresses long values; keeps in the row if at all possible, moving out only as a last resort.

All strategies except plain are compatible with TOAST (The Oversized-Attribute Storage Technique) and set the column's default TOAST strategy. Override this per column with ALTER TABLE SET STORAGE.

Array type (automatic)

When a new type is defined, PolarDB automatically creates a corresponding array type. The array type name is the element type name prefixed with an underscore (_). If the name would exceed the NAMEDATALEN limit, it is truncated, and PolarDB tries alternate names to avoid conflicts.

The auto-created array type:

Always has variable length
Uses the built-in array_in and array_out functions
Is automatically dropped when the element type is dropped

Even though PolarDB auto-creates the array type, still set ELEMENT when creating a fixed-length type that is internally a fixed array of identical elements and you want subscript access. For such types, subscripts start at 0 (for historical reasons). For variable-length array types, subscripts start at 1.

Parameters

General parameters

Parameter	Description
`name`	The name of the type, optionally schema-qualified.
`attribute_name`	The name of an attribute in a composite type.
`data_type`	The data type of an attribute in a composite type.
`collation`	The collation for an attribute in a composite type, or for the subtype in a range type.

Enumerated type parameters

Parameter	Description
`label`	A quoted string that represents one value of the enumerated type. Each label is unique within the type.

Range type parameters

Parameter	Description
`subtype`	The element type the range is built on.
`subtype_operator_class`	The B-tree operator class to use for ordering subtype values.
`canonical_function`	A function that converts range values to their canonical representation.
`subtype_diff_function`	A function that returns the difference between two subtype values as `double precision`.

Base type parameters

Parameter	Description
`input_function`	Converts the external text representation to the internal representation.
`output_function`	Converts the internal representation to the external text representation.
`receive_function`	Converts the external binary representation to the internal representation.
`send_function`	Converts the internal representation to the external binary representation.
`type_modifier_input_function`	Validates and encodes type modifiers into the `typmod` integer.
`type_modifier_output_function`	Converts `typmod` to a human-readable string.
`analyze_function`	Collects statistics on the type for the query planner.
`internallength`	Length in bytes of the internal representation, or `VARIABLE`.
`alignment`	Memory alignment: `char`, `int2`, `int4`, or `double`. Default: `int4`.
`storage`	Storage strategy: `plain`, `external`, `extended`, or `main`. Default: `plain`.
`like_type`	An existing type whose `internallength`, `passedbyvalue`, `alignment`, and `storage` attributes are copied.
`category`	Single ASCII character identifying the type's category. Default: `U`.
`preferred`	Whether this type is the preferred implicit cast target in its category. Default: `false`.
`default`	Default value for columns of this type.
`element`	Element type for subscript-accessible fixed-length array types.
`delimiter`	Delimiter character for array values in external representation. Default: `,`. The delimiter is a property of the element type, not the array type.
`collatable`	Whether collation information can be attached to this type. Default: `false`.

Usage notes

Avoid naming types or tables with a leading underscore (_). PolarDB uses the _typename convention for auto-generated array type names, so using the same pattern manually risks naming conflicts.

Examples

Create and use a composite type

This example creates a composite type and uses it as the return type of a function.

CREATE TYPE compfoo AS (f1 int, f2 text);

CREATE FUNCTION getfoo() RETURNS SETOF compfoo AS $$
    SELECT fooid, fooname FROM foo
$$ LANGUAGE SQL;

Update a composite type definition

Use OR REPLACE to redefine the type in place. This example swaps the attribute order.

CREATE TYPE compfoo AS (f1 int, f2 text);

CREATE OR REPLACE TYPE compfoo AS (f2 text, f1 int);

Create and use an enumerated type

This example creates an enumerated type representing bug states and uses it as a column type.

CREATE TYPE bug_status AS ENUM ('new', 'open', 'closed');

CREATE TABLE bug (
    id serial,
    description text,
    status bug_status
);

Create a range type

CREATE TYPE float8_range AS RANGE (subtype = float8, subtype_diff = float8mi);

Create a base type

This example creates a base type named box, registers its I/O functions, then completes the type definition.

-- Step 1: Create a shell type as a forward reference
CREATE TYPE box;

-- Step 2: Register I/O functions that reference the shell type
CREATE FUNCTION my_box_in_function(cstring) RETURNS box AS ... ;
CREATE FUNCTION my_box_out_function(box) RETURNS cstring AS ... ;

-- Step 3: Complete the type definition, replacing the shell type
CREATE TYPE box (
    INTERNALLENGTH = 16,
    INPUT = my_box_in_function,
    OUTPUT = my_box_out_function
);

-- Use the new type as a column type
CREATE TABLE myboxes (
    id integer,
    description box
);

If box is internally an array of four float4 elements and you want subscript access, add ELEMENT:

CREATE TYPE box (
    INTERNALLENGTH = 16,
    INPUT = my_box_in_function,
    OUTPUT = my_box_out_function,
    ELEMENT = float4
);

Create a large object type

CREATE TYPE bigobj (
    INPUT = lo_filein, OUTPUT = lo_fileout,
    INTERNALLENGTH = VARIABLE
);

CREATE TABLE big_objs (
    id integer,
    obj bigobj
);