Restrictions on Serial and Simple-Large-Object Data Types

Serial and simple-large-object data types cannot be nested within a table. Therefore, if a ROW type contains a BYTE, TEXT, SERIAL, BIGSERIAL, or SERIAL8 field, you cannot use the ROW type to define a column in a table that is not based on a ROW type. For example, the following code example produces an error:

CREATE ROW TYPE serialtype (s serial, s8 serial8);
CREATE TABLE tab1 (col1 serialtype); --INVALID CODE

You cannot create a ROW type that has a BYTE or TEXT value that is stored in a separate storage space. That is, you cannot use the IN clause to specify the storage location. For example, the following example produces an error:

CREATE ROW TYPE row1 (field1 byte IN blobspace1); --INVALID CODE

A table hierarchy can include no more than one SERIAL, BIGSERIAL, or SERIAL8 column. If a supertable has a SERIAL column, none of its subtables can contain a SERIAL column (but a subtable can have a BIGSERIAL or SERIAL8 column if no other subtable contains a BIGSERIAL or SERIAL8 column, respectively). Consequently, when you create the named ROW types on which the table hierarchy is to be based, they can contain at most one SERIAL and one BIGSERIAL or SERIAL8 field among them.

You cannot set the starting SERIAL, BIGSERIAL, or SERIAL8 value in the CREATE ROW TYPE statement. To modify the value for a serial field, you must use either the MODIFY clause of the ALTER TABLE statement, or else use the INSERT statement to insert a value that is larger than the current maximum (or default) serial value.

Serial fields in ROW types have performance implications across a table hierarchy. To insert data into a subtable whose supertable (or its supertable) contains the serial counter, the database server must also open the supertable, update the serial value, and close the supertable, thus adding extra overhead.

In contexts where these restrictions or performance issues for SERIAL, BIGSERIAL, or SERIAL8 data types conflict with your design goals, you might consider using sequence objects to emulate the functionality of serial fields or serial columns.