Composite types

Declaration of composite types

Syntax

CREATE TYPE type_name AS (
    attribute_name data_type [, ...]
);

Parameters

Examples

CREATE TYPE complex AS (
    r   double precision,
    i   double precision
);

CREATE TYPE inventory_item AS (
    name        text,
    supplier_id integer,
    price       numeric
);

The syntax is similar to CREATE TABLE, with two key differences:

• Only field names and data types are allowed — constraints such as NOT NULL are not supported.

• The AS keyword is required. Without it, the parser interprets the statement as a different form of CREATE TYPE.

Automatic row types

Every CREATE TABLE statement automatically creates a composite type with the same name as the table, representing the table's row type. For example:

CREATE TABLE inventory_item (
    name        text,
    supplier_id integer REFERENCES suppliers,
    price       numeric CHECK (price > 0)
);

This creates both the table and an inventory_item composite type. However, constraints defined on the table (such as CHECK or NOT NULL) do not apply to the composite type when used outside the table. To enforce constraints on values of the composite type, create a domain over the composite type and apply CHECK constraints to the domain.

Using composite types in tables and functions

-- Use the composite type as a column type
CREATE TABLE on_hand (
    item  inventory_item,
    count integer
);

INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);

-- Use the composite type in a function signature
CREATE FUNCTION price_extension(inventory_item, integer) RETURNS numeric
AS 'SELECT $1.price * $2' LANGUAGE SQL;

SELECT price_extension(item, 10) FROM on_hand;

Constructing composite values

Use either the string literal syntax or the ROW constructor syntax to write composite values.

'( val1 , val2 , ... )'

'("fuzzy dice", 42, 1.99)'   -- all fields set
'("fuzzy dice", 42,)'        -- third field is NULL
'("", 42,)'                  -- first field is an empty string, third is NULL

ROW('fuzzy dice', 42, 1.99)
ROW('', 42, NULL)

('fuzzy dice', 42, 1.99)
('', 42, NULL)

Accessing composite types

Use dot notation to access a field of a composite column, and wrap the column reference in parentheses to avoid parser ambiguity:

-- This fails: the parser treats "item" as a table name, not a column
SELECT item.name FROM on_hand WHERE item.price > 9.99;

-- Correct: parentheses make "item" a column reference
SELECT (item).name FROM on_hand WHERE (item).price > 9.99;

-- With an explicit table name (for multitable queries)
SELECT (on_hand.item).name FROM on_hand WHERE (on_hand.item).price > 9.99;

Similarly, when accessing a field from a composite value returned by a function, parenthesize the function call:

SELECT (my_func(...)).field FROM ...;

The special field name * means all fields.

Functional notation

Field access and function-call syntax are interchangeable for composite types. The following two queries are equivalent:

SELECT c.name FROM inventory_item c WHERE c.price > 1000;
SELECT name(c) FROM inventory_item c WHERE price(c) > 1000;

This equivalence makes it possible to implement computed fields using functions on composite types — the caller does not need to know whether somefunc is a real column or a function:

SELECT somefunc(c) FROM inventory_item c;
SELECT somefunc(c.*) FROM inventory_item c;
SELECT c.somefunc FROM inventory_item c;

Note: Avoid giving a function the same name as any field of the composite type it takes as an argument. When field-name syntax is used and names conflict, the field-name interpretation takes precedence. Schema-qualify the function name to force the function interpretation: schema_name.function_name(composite_value). Note that in PostgreSQL versions before 11, the field-name interpretation was always chosen when field-name syntax was used, regardless of whether a function with the same name existed — schema-qualifying was the only way to force the function call in those older versions.

Modifying composite types

Update an entire composite column

INSERT INTO mytab (complex_col) VALUES ((1.1, 2.2));

UPDATE mytab SET complex_col = ROW(1.1, 2.2) WHERE ...;

Update a single subfield

UPDATE mytab SET complex_col.r = (complex_col).r + 1 WHERE ...;

After SET, the column name appears without parentheses. When referencing the same column on the right side of the assignment, wrap it in parentheses: (complex_col).r.

Insert with subfield targets

INSERT INTO mytab (complex_col.r, complex_col.i) VALUES (1.1, 2.2);

Subfields not supplied in the column list are set to NULL.

Using composite types in queries

In PolarDB for Oracle, a reference to a table name (or alias) in a query is effectively a reference to the composite value of the current row. For example:

SELECT c FROM inventory_item c;

This produces a single composite-valued column:

           c
------------------------
 ("fuzzy dice",42,1.99)
(1 row)

Note that simple names match column names before table names, so this works only when no column named c exists in the query's tables.

Column expansion with .*

Use c.* to expand a composite value into its individual columns, as required by the SQL standard:

SELECT c.* FROM inventory_item c;

Output:

    name    | supplier_id | price
------------+-------------+-------
 fuzzy dice |          42 |  1.99
(1 row)

This is equivalent to:

SELECT c.name, c.supplier_id, c.price FROM inventory_item c;

PolarDB for Oracle implements .* expansion by internally rewriting the query into the per-field form. When applied to a function returning a composite type, this means the function is called once per field per row:

-- myfunc() is called three times per row
SELECT (myfunc(x)).* FROM some_table;

-- Equivalent, but explicit
SELECT (myfunc(x)).a, (myfunc(x)).b, (myfunc(x)).c FROM some_table;

To avoid repeated invocations of an expensive function, use a LATERAL subquery:

SELECT m.* FROM some_table, LATERAL myfunc(x) AS m;

The LATERAL keyword is optional here but clarifies that the function receives x from some_table.

.* expansion applies at the top level of a SELECT output list, a RETURNING list in INSERT/UPDATE/DELETE, a VALUES clause, or a row constructor. In all other contexts, .* attached to a composite value has no effect — the same composite value is returned.

Ordering by composite value

The following three ORDER BY clauses are equivalent — all sort rows by their full composite value:

SELECT * FROM inventory_item c ORDER BY c;
SELECT * FROM inventory_item c ORDER BY c.*;
SELECT * FROM inventory_item c ORDER BY ROW(c.*);

These are also equivalent to:

SELECT * FROM inventory_item c ORDER BY ROW(c.name, c.supplier_id, c.price);
SELECT * FROM inventory_item c ORDER BY (c.name, c.supplier_id, c.price);

If inventory_item contained a column named c, the first form (ORDER BY c) would sort by that column only, not by the full composite value.

Composite type input and output syntax

The text representation of a composite value consists of field values separated by commas and enclosed in parentheses. Whitespace outside the parentheses is ignored; whitespace inside is considered part of the field value and may be significant depending on the field's data type. For example, '( 42)' ignores the leading space if the field is integer, but preserves it if the field is text.

Quoting and escaping

Double-quote field values that contain parentheses, commas, double quotes, backslashes, or whitespace. To embed a double quote or backslash in a quoted field value, precede it with a backslash. A pair of double quotes inside a double-quoted field represents a single double quote, analogous to single-quote rules for SQL string literals.

NULL vs. empty string

Backslash doubling

When writing composite literals inside an SQL command, the string-literal parser processes backslashes first, before the composite-value parser sees them. This means you need to double backslashes. To insert a text field containing a double quote and a backslash, write:

INSERT ... VALUES ('("\"\\")');

The string-literal parser reduces this to ("\"\\"), and the composite-value parser then produces "\ as the field value. (For data types whose input routine also treats backslashes specially — bytea, for example — you may need as many as eight backslashes in the command to store a single backslash in the composite field.) Use dollar quoting to avoid this doubling:

INSERT ... VALUES ($$("\"\\")$$);