CREATE TYPE

Syntax

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

CREATE TYPE name;

Composite type

A composite type is a named set of attribute–type pairs, similar to a table's row type. Unlike a table, a composite type has no storage of its own—it exists purely as a type definition for use in function signatures, return values, and column definitions.

-- Define a composite type
CREATE TYPE compfoo AS (f1 int, f2 text);

-- Use it as a function return type
CREATE FUNCTION getfoo() RETURNS SETOF compfoo AS $$
    SELECT fooid, fooname FROM foo
$$ LANGUAGE SQL;

OR REPLACE behavior

OR REPLACE overwrites an existing composite type with the same name. Use it to update a type definition without dropping and recreating it.

Permissions

To create a composite type, you need the USAGE privilege on all data types used as attribute types.

Enumerated type

An enumerated type is an ordered list of string labels. Labels must be enclosed in quotation marks and are limited to NAMEDATALEN bytes (64 bytes by default).

-- Define an enumerated type
CREATE TYPE bug_status AS ENUM ('new', 'open', 'closed');

-- Use it as a column type
CREATE TABLE bug (
    id serial,
    description text,
    status bug_status
);

An enumerated type can be created with no labels. It cannot store values until at least one label is added via ALTER TYPE ... ADD VALUE.

Range type

A range type represents a continuous interval of values from a subtype. The subtype must have a B-tree operator class to define value ordering—the default B-tree operator class is used unless you specify otherwise.

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

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

-- Step 2: Define the canonical function referencing the shell type
CREATE FUNCTION myrange_canonical(myrange) RETURNS myrange AS ... ;

-- Step 3: Replace the shell type with the full definition
CREATE TYPE myrange AS RANGE (
    SUBTYPE = integer,
    CANONICAL = myrange_canonical
);

Base type

A base type (also called a scalar type) defines entirely new internal storage behavior. Creating a base type requires superuser privileges.

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

-- Step 2: Define the required I/O functions
CREATE FUNCTION my_box_in_function(cstring) RETURNS box AS ... ;
CREATE FUNCTION my_box_out_function(box) RETURNS cstring AS ... ;

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

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

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

Array types

When you create any new type, PolarDB automatically creates a corresponding array type. The array type is named by prefixing the element type name with an underscore (_). If the generated name exceeds NAMEDATALEN, PolarDB truncates and adjusts it to avoid conflicts.

The auto-created array type:

• Has variable length

• Uses built-in functions array_in and array_out

• Shares the ownership and schema of its element type

• Is deleted automatically when the element type is deleted

Even though PolarDB auto-creates array types, the ELEMENT option is still required when creating a fixed-length type that is essentially an array of multiple identical elements and you want to allow direct subscript access to individual elements. For fixed-length array types, subscripts start from 0. For variable-length array types, subscripts start from 1.

Usage notes

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

• The parameters of CREATE TYPE for base types can be specified in any order.

• An empty enumerated type (no labels) is valid but cannot store values until labels are added with ALTER TYPE.

Examples

Create a composite type for use as a function return type

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

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

Update an existing composite type definition

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

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

Create an enumerated type and use it in a table

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

CREATE TYPE box;

CREATE FUNCTION my_box_in_function(cstring) RETURNS box AS ... ;
CREATE FUNCTION my_box_out_function(box) RETURNS cstring AS ... ;

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

CREATE TABLE myboxes (
    id integer,
    description box
);

Create a large object type

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

CREATE TABLE big_objs (
    id integer,
    obj bigobj
);