The nextval() is a built-in function in PostgreSQL that allows the use of sequences on the database to automatically set values by incrementing the set of default values. Sequences are the storage type that can be used to manage data efficiently on the databases and also store data on them.

This post will demonstrate the use of the nextval() function in PostgreSQL.

What Does the nextval() Function Do in PostgreSQL?

In PostgreSQL, the purpose of the nextval() function is to generate the next available value from a sequence when it is invoked/called. The sequence is the series of values or a list of integers containing values in ascending or descending order. The sequence is different if the order of numbers is different from each other for instance {1,2,3} and {3,2,1} are two different sequences.

Syntax

Use this syntax/code to create a sequence in PostgreSQL:

CREATE SEQUENCE [ IF NOT EXISTS ] seq_name
  [ AS { SMALLINT | BIGINT | INTEGER } ]
  [ INCREMENT [ BY ] increment_value ]
  [ MINVALUE minimum_value | NO MINVALUE ] 
  [ MAXVALUE maximum_value | NO MAXVALUE ]
  [ START [ WITH ] initial_val ] 
  [ CACHE allocated_cache_size ] 
  [ [ NO ] CYCLE ]
  [ OWNED BY { t_name.col_name | NONE } ]

Here:

- The query starts with CREATE SEQUENCE keywords followed by the name of the sequence.
- Set the data type contained by the AS keyword however, if the user doesn't set any data type the default datatype is ‘BIGINT’ for sequence.
- The INCREMENT keyword is used to increase the value by adding it to the current value.
- The user can set MAXVALUE and MINVALUE to mark the end of the sequence but it takes the default value if NO MINVALUE or NO MAXVALUE is selected.
- The START keyword specifies where the sequence starts from.
- CACHE is an optional operator which explains how many preallocated sequence numbers are available in the memory.
- If CYCLE is specified, the sequence will start again from the MINVALUE once the MAXVALUE is reached. If not specified, calling nextval() after reaching the MAXVALUE will result in an error.
- OWNED BY specifies which table and column should "own" the sequence, which means that the sequence will be automatically dropped if the owning table or column is dropped.

Example 1: Creating Ascending Sequence to Use nextval() Function

Use the following query to create the sequence in PostgreSQL:

CREATE SEQUENCE firstsequence
 INCREMENT 10
 START 50;

This code suggests:

- It creates a sequence by providing the name of the sequence.
- The starting value will be 50 and for each increment 10 will be added to the current sequence:

Use the following query to check the value of the sequence:

SELECT nextval('firstsequence');

Running the above query will display the starting value of the sequence:

Run again the same query to find the next value after incrementing to the previous value which in this sequence is “60”:

Example 2: Creating Descending Sequence to Use nextval() Function

Use the following query to create a descending sequence that adds “-5” on every increment to the current value starting from the 5:

CREATE SEQUENCE five
 INCREMENT -5
 MINVALUE 1 
   MAXVALUE 10
 START 5
 CYCLE;

The sequence has been created successfully which starts at 5 and ends at 1 and increments -5. The CYCLE clause determines that the sequence never stops as it will start from 1 upon reaching 10 every time. So the sequence will contain values like {5, 10, 5, 10, 5, 10,...} and the cycle keeps on going:

Use the following query to get the starting value of the sequence:

SELECT nextval('five');

The above query will generate value 5 which is the starting value of the sequence:

Running the function again will increment "-5", which ideally should display 0. However, since it exceeds the defined bounds, the function displays 10:

Running the query again will display value 5 after incrementing -5 in the current value which is 10:

Example 3: Sequence With Table to Use nextval() Function

Use the following query to create a new table and then create a sequence on its field:

CREATE TABLE customer (
  id integer PRIMARY KEY,
  name varchar(50),
  age integer,
  email varchar(50)
   );

The table named customer has been created successfully with multiple columns such as id, name, age, and email:

Use the following query to create a sequence named customer_id_seq which starts from 1 and everything else is the default:

CREATE SEQUENCE customer_id_seq   
START 1;

The sequence has been created successfully:

Use the following query to attach the sequence to the id column of the customer table:

ALTER TABLE customer 
 ALTER COLUMN id SET DEFAULT nextval('customer_id_seq');

Once the seq is set to the id column, there is no need to insert values in it as it automatically takes value by incrementing on inserting values on the table:

INSERT INTO   customer (name, age, email) 
 VALUES ('John Doe', 35, 'johndoe@example.com'), 
  ('Jane Smith', 42, 'janesmith@example.com'), 
  ('Bob Johnson', 28, 'bobjohnson@example.com');

Use the following query to fetch all columns with data from the table customer:

SELECT * FROM customer;

The values have been added to the table and it automatically uses the sequence to set values in the id column:

That’s all about the nextval() function in PostgreSQL.

Conclusion

The nextval() function in PostgreSQL is used to access the next value in the list/sequence by incrementing or adding the value to the existing one. The sequence can be created in ascending or descending order containing the incremental value to be added to the sequence on invoking the nextval() function. This guide has explained the use of the nextval() function in PostgreSQL with multiple examples.