Collection types v15

A collection is a set of ordered data items with the same data type. Generally, the data item is a scalar field. It can also be a user-defined type such as a record type or an object type. In this case, the structure and the data types that make up each field of the user-defined type must be the same for each element in the set. Reference each data item in the set by using subscript notation inside a pair of parentheses.

The most commonly known type of collection is an array. In EDB Postgres Advanced Server, the supported collection types are:

The general steps for using a collection are the following:

  • Define a collection of the desired type. You can do this in the declaration section of an SPL program, which results in a local type that you can access only in that program. For nested table and varray types, you can also do this using the CREATE TYPE command, which creates a persistent, standalone type that any SPL program in the database can reference.
  • Declare variables of the collection type. The collection associated with the declared variable is uninitialized at this point if no value assignment is made as part of the variable declaration.
  • Uninitialized collections of nested tables and varrays are null. A null collection doesn't yet exist. Generally, a COLLECTION_IS_NULL exception is thrown if a collection method is invoked on a null collection.
  • Uninitialized collections of associative arrays exist but have no elements. An existing collection with no elements is called an empty collection.
  • To initialize a null collection, you must either make it an empty collection or assign a non-null value to it. Generally, a null collection is initialized by using its constructor.
  • To add elements to an empty associative array, you can assign values to its keys. For nested tables and varrays, generally its constructor is used to assign initial values to the nested table or varray. For nested tables and varrays, you then use the EXTEND method to grow the collection beyond its initial size set by the constructor.


  • Multilevel collections (that is, where the data item of a collection is another collection) aren't supported.

  • Columns of collection types aren't supported.

    For example, you create an array varchar2_t but you can't create a table using array varchar2_t as a column data type.

    --Create an array 
    edb=# CREATE TYPE varchar2_t AS TABLE OF character varying;
    --Create a table using array as the column data type
    edb=# CREATE TABLE t(a varchar2_t);
    ERROR:  column "a" has collection type varchar2_t, columns of collection types are not supported.

associative_arrays nested_tables varrays