PostgreSQL Sequence

A sequence in PostgreSQL is a database object that is essentially an automatically incrementingnumeric value. For this reason, sequences are commonly known in other database products as auto-incrementvalues. Sequences can be extremely useful in assigning non-random, unique identification numbers to tables that requiresuch values.A sequence consists of a current numeric value, and a set of characteristics that determine how to automatically increment(or alternatively, decrement) that value upon use.
Along with its current value, a sequence also includes a minimum value, a maximum value, a starting value, and theamount to increment the sequence by. This increment is usually 1, but may beany whole integer.
In practice, sequences are not meant to be accessed directly. Instead, they are used through a set of functions builtinto PostgreSQL which either set, increment, or return the current value of the sequence.

Creating a sequence

Sequences are created with the CREATE SEQUENCE SQL command. The sequence can bespecified to increment or decrement. The syntax for CREATE SEQUENCE is:

  CREATE SEQUENCE sequencename [ INCREMENT increment ] [ MINVALUE minvalue ] [ MAXVALUE maxvalue ] [ START start ] [ CACHE cache ] [ CYCLE ]

In this syntax, sequencename is the name of the sequence to be created. This is the onlyrequired parameter. A sequence uses the integer data type, and it therefore shares its maximum and minimum limitations of2147483647 and –2147483647, respectively.
The optional CREATE SEQUENCE clauses are as follows:

INCREMENT increment_val

Sets the numeric quantity with which to modify the sequence's value to increment_val. This isused when the nextval() function is called on the sequence. Settingincrement_val to a negative number results in a descending sequence. The default value is 1.

MINVALUE minvalue

Sets the fixed minimum value for the sequence to minvalue. Any attempt to lower asequence below this value will result in an error, or in the value cycling to its maximum value (ifthe CYCLE keyword was used when the sequence was created).
The default value is 1 for ascending sequences, and –2147483647 for descending sequences.

MAXVALUE maxvalue

Sets the fixed maximum value for the sequence to maxvalue. Any attempt to raisea sequence above this value will result in an error, or in the value cycling to its minimum value.
The default value is 2147483647 for ascending sequences, and –1 for descending sequences.

START start_val

Sets the value that the sequence begins at. It may be any integer between the minimum and maximum values. The sequence defaults to start at its minimum value for ascending sequences, and its maximum value for descendingsequences.

CACHE cache

Provides the ability for sequence values to be pre-allocated and stored in memory. This can resultin faster access times to highly used sequences. The minimum and default value is 1; a highervalue of cache results in more values being cached.

CYCLE

Enables the sequence to continue generating new values after it has reached its maximum or minimum value. When thelimit is reached, the sequence starts over at the minimum value (for ascending sequences), or at the maximum value (descendingsequences).

Example 7-28 creates a simple ascending sequence namedshipments_ship_id_seq that starts at a value of 0, and will beincremented by the default increment of 1 until it reaches the default maximum limit of2147483647. By not using the CYCLE keyword, the sequence isguaranteed to always return a unique value.

Example 7-28. Creating a sequence

postgres=# CREATE SEQUENCE shipments_ship_id_seq MINVALUE 0;CREATE SEQUENCE

Viewing a sequence

The output from the \d command within psql shows whether ornot a database object is a sequence, table, view or index. More specifically, the \dscommand can be used to view all sequences in the currently connected database. For example:

postgres=# \ds  List of relations Schema | Name  | Type |  Owner--------+-----------------------+----------+---------- public | shipments_ship_id_seq | sequence | postgres(1 row)

While not often necessary, sequences can be directly queried with SELECTstatements, as if they were a table or view.When you query a sentence, you use the attributes of that sequence as columns in your select list.The attributes of a sequence are shown in Table 7-1.

Table 7-1. Sequence attributes

Attribute	Type
sequence_name	name
last_value	integer
increment_by	integer
max_value	integer
min_value	integer
cache_value	integer
log_cnt	integer
is_cycled	"char"
is_called	"char"

 Example 7-29 illustrates a query to the shipments_ship_id_seqsequence. This query selects the last_value attribute, which is the most currentlyselected value from the sequence, and the increment_by attribute, which is the amount thesequence is to be incremented each time the nextval() function is called.

Example 7-29. Viewing a sequence

postgres=# \d shipments_ship_id_seq Sequence "public.shipments_ship_id_seq"  Type  | Start | Minimum | Maximum | Increment | Cycles? | Cache--------+-------+---------+---------------------+-----------+---------+------- bigint | 0 | 0 | 9223372036854775807 | 1 | no  | 1

postgres=# select *from shipments_ship_id_seq; last_value | log_cnt | is_called------------+---------+-----------  0 | 0 | f(1 row)

Since the sequence in question has just been created, its last_value is still set to0.

Using a sequence

Sequences are typically not queried directly, but are instead used through functions. There are three functions inPostgreSQL which apply exclusively to sequences:

nextval('sequence_name')

Increments the value of the specified sequence named sequence_name, and returns the new value,which is of type integer.

currval('sequence_name')

Returns the most recently returned value from nextval('sequence_name'). This valueis associated with a PostgreSQL session, and if the nextval() function has not yetbeen called in the connected session on the specified sequence sequence_name, there will be no"current" value returned.

setval('sequence_name', n )

Sets the current value of the specified sequence to the numeric value n.The value returned by the next call to nextval() will returnn + increment, where increment is the amount that the sequence incrementsby each iteration.

setval('sequence_name', n, b )

Also sets the current value of the specified sequence to the numeric value n. However, ifb (a value of type boolean) is false,the value returned by the next call to nextval() will be justn . If b is true, the next call tonextval() will return n + increment, as it would withoutspecifying the Boolean argument at all.

The most commonly used sequence function is nextval(). This is the function thatactually pushes the increment of the value. It requires the name of the sequence as the argument (bound by singlequotes), and returns a value of type integer.
Example 7-30 selects a couple of incremented values from the sequence namedshipments_ship_id_seq.

Example 7-30. Incrementing a sequence

postgres=# SELECT nextval('shipments_ship_id_seq'); nextval--------- 0(1 row)postgres=# SELECT nextval('shipments_ship_id_seq'); nextval--------- 1(1 row)postgres=# SELECT nextval('shipments_ship_id_seq'); nextval--------- 2(1 row)

Note: The first call to nextval() will return the sequence's initial value(set by the START keyword), since it has not yet been called to increment the startingvalue. All subsequent calls increment the last_value column.

Sequences are commonly used as default values for tables which require unique integer identifiers. Theshipments table within the booktown database, shown inTable 7-2, exemplifies this.

Table 7-2. The shipments table

Column	Type	Modifier
id	integer	NOT NULL DEFAULT nextval('shipments_ship_id_seq')
customer_id	integer
isbn	text
ship_date	timestamp with time zone

 
 The syntax to create the table in Table 7-2, with the auto-incrementingDEFAULT and PRIMARY KEY constraint, is:

  CREATE TABLE shipments (id integer DEFAULT nextval('"shipments_ship_id_seq"'::text) PRIMARY KEY,  customer_id integer,  isbn text,  ship_date timestamp);

The default value for the id column in Table 7-2 is setto the nextval()'s result on the shipments_ship_id_seqsequence. Insertion of row data that does not specify a value for idwill therefore choose its value from the result of this function call.

Warning

Merely placing a DEFAULT constraint on the id column does not enforce the use of that default. A user could still manually insert a value, potentially causing a conflict with future sequence values. This can be disallowed with the use of a trigger. See the Section called Triggers " later in this chapter for more information.

After the nextval() function has been called on a sequence in a given session(a connection to PostgreSQL), the currval() function may be used on that same sequence toreturn the most recently returned value from the sequence. Note that this function may only becalled on a sequence that has been called through nextval() in the activesession.

Note: Sequences' "current" values are associated with sessions in order to prevent multiple users from running intomistakes by accessing the same sequence at the same time. Two users may access the same sequence from separatesessions, but the currval() function will return only the most recently incrementedvalue of the sequence from within the same session that calls currval().

Example 7-31 inserts a new row into the shipments column, withoutspecifying the value for the id column. This causes the default value to be used, which(as noted in Table 7-2) is the result of theshipments_ship_id_seq being incremented by thenextval() function. The currval() function is thenused to access the row that was just inserted.

Example 7-31. Using currval( )

postgres=# INSERT INTO shipments (customer_id, isbn, ship_date) VALUES (221, '0394800753', 'now');INSERT 0 1postgres=# SELECT * FROM shipments WHERE id = currval('shipments_ship_id_seq');  id  | customer_id |isbn| ship_date------+-------------+------------+------------------------ 1002 | 107 | 0394800753 | 2001-09-22 11:23:28-07(1 row)

Finally, a sequence may also have its last_value attribute reset to an arbitrarynumeric value (within its maximum and minimum value range) by using the setval()function. This requires the name of the sequence as a single-quote bound character string for the first argument and aninteger constant representing the new value for last_value for the second argument.
There are two ways to go about this. By default, setval() assumes that the newsetting is for an initialized sequence; this means that the next value returned bynextval() will actually be incremented once past the value set bysetval().
Alternatively, an optional false value of type boolean may beadded as the last argument to setval(), de-initializing the sequence. This modifies thesequence so that the next value returned by nextval() will be the same numeric valuepassed to setval() (though the sequence will of course be incremented on the next callto nextval()).
Example 7-32 sets the shipments_ship_id_seq'slast_value to 1010 through each method, and selects thenextval() on the same sequence to illustrate the effective result.

Example 7-32. Setting a sequence value

postgres=# SELECT setval('shipments_ship_id_seq', 1010); setval-------- 1010(1 row)postgres=# SELECT nextval('shipments_ship_id_seq'); nextval---------1011(1 row)postgres=# SELECT setval('shipments_ship_id_seq', 1010, false); setval-------- 1010(1 row)postgres=# SELECT nextval('shipments_ship_id_seq'); nextval---------1010(1 row)postgres=# SELECT nextval('shipments_ship_id_seq'); nextval---------1011(1 row)

Warning

Sequences are commonly used to ensure unique values in a column. Be sure that you understand the application of a sequence before you reset its last_value attribute.

Destroying a sequence

To destroy a sequence, or several sequences simultaneously, use the DROP SEQUENCESQL command. Here is the syntax for DROP SEQUENCE:

  DROP SEQUENCE sequencename [, ...]

In this syntax, sequencename is the name of the sequence that you wish to remove. Multiplesequence names may be specified, separated by commas.
Example 7-33 removes the shipments_ship_id_seqsequence.

Example 7-33. Removing a sequence

postgres=# DROP SEQUENCE shipments_ship_id_seq;DROP SEQUENCE

Before destroying a sequence, make sure that the sequence is not used by another table, function, or any otherobject in the database. If this check is not performed, then other operations that rely on the sequence will fail. Thefollowing query will return the name of any relation which relies on a default sequence value, wheresequence_name is the name of the sequence you are interesting in finding dependencies for:

  SELECT p.relname, a.adsrc FROM pg_class p  JOIN pg_attrdef a ON (p.relfilenode = a.adrelid) WHERE a.adsrc  '"sequence_name "';

Example 7-34 uses this query to look up the name of any table with a default valueinvolving the shipments_ship_id_seq sequence.

Example 7-34. Checking sequence dependencies

SELECT p.relname, a.adsrc FROM pg_class p JOIN pg_attrdef aON (p.relfilenode = a.adrelid)WHERE a.adsrc  '"shipments_ship_id_seq"';  relname  |adsrc-----------+------------------------------------------------------ shipments | nextval(('"shipments_ship_id_seq"'::text)::regclass)(1 row)