NEXTVAL

Returns the next value in a sequence. Call NEXTVAL after creating a sequence to initialize the sequence with its default value. Thereafter, call NEXTVAL to increment the sequence value for ascending sequences, or decrement its value for descending sequences.

Behavior Type

Volatile

Syntax

NEXTVAL('[[database.]schema.]sequence')

Parameters

[database.]schema

Specifies a schema, by default public. If schema is any schema other than public, you must supply the schema name. For example:

myschema.thisDbObject

If you specify a database, it must be the current database.

sequence

Identifies the target sequence.

Privileges

  • SELECT privilege on sequence
  • USAGE privilege on sequence schema

Restrictions

You cannot invoke NEXTVAL in a SELECT statement, in the following contexts:

  • WHERE clause
  • GROUP BY clause
  • ORDER BY clause
  • DISTINCT clause
  • UNION
  • Subquery

You also cannot invoke NEXTVAL to act on a sequence in:

  • UPDATE or DELETE subqueries
  • Views

You can use subqueries to work around some of these restrictions. For example, to use sequences with a DISTINCT clause:

=> SELECT t.col1, shift_allocation_seq.NEXTVAL FROM (
     SELECT DISTINCT col1 FROM av_temp1) t;

Examples

See Creating and Using Named Sequences in the Administrator's Guide

See Also

CURRVAL