Sequence Functions in SQL
When we use a column in our table that has sequential values like a serial number or a roll number that increments with a pattern, we can create a sequence to encounter the problem. In this sequence, we will give several constraints according to our needs. The SQL syntax for creating a sequence is as follows:
The name of the series must be written against the ” CREATE SEQUENCE ” command in the above syntax. The starting value of the sequence will then be written to the ” START WITH ” command, followed by the incremental value to the ” INCREMENT BY ” command. The ” MINVALUE ” and “ MAXVALUE” commands will be used to establish the sequence’s minimum value or maximum value. We will explain whether the sequence should end when it reaches the maximum value or return to the original value in the last line of the sequence.
When we have to alter a sequence in between due to some constraints or errors in the program, we use several sequence functions in PostgreSQL. They are also referred to as the Sequence Manipulation Functions. The functions that are associated with the concept of sequence function are as follows:
- Nextval function.
- Currval function.
- Lastval function.
- Setval function.
In this guide, we will be talking over the Setval function in PostgreSQL.
Setval Sequence Manipulation Function
The Setval function resets the current sequence counter variable that keeps incrementing as per the constraints set by the user and gives it a check at how the sequence should proceed after this point. The Setval function is adjustable to our needs and can take different sets of parameters like the name of the sequence, the next value that the nextval function would return, and the Boolean algebra constraint of true and false.
The Setval function is typically used in association with the ” SELECT ” command, in which the user specifies the constraints and then specifies the order in which the change must be made inside the table.
The Setval function manipulates the next value of the nextval function as it takes the current nextval value as a parameter. If the third parameter that is the state of the value that can either be true or false is determined, then the next nextval value is altered according to the given state that is compiled at the runtime when the sequence is located. Let’s look at the syntax for executing this manipulation function in PostgreSQL:
The above syntax for executing this function in PostgreSQL can also be written as:
We can choose both these expressions to execute the Setval function in the PostgreSQL environment, but the second expression does not take in the Boolean check as the parameter and makes it a less refined state of the function.
Now that we know the syntax and the working of the Setval function, we will look into the core purpose this function serves in the PostgreSQL environment.
Manipulation of a Sequence by the Setval Function
As we discussed above the concept of sequences and how we create a sequence. In this case, we will create a sequence first to understand and manipulate that sequence by the Setval function. We will create a sequence for roll numbers of students in one class by writing this code as shown in the below snapshot:
As you can see from the above syntax, we have constructed a sequence called ” sq 1 ” that begins with the number 001 and is increased by one at each iteration. The minimum value for this sequence is 0 while the maximum value is 100. This sequence goes in a cycle which means when the maximum value is achieved it will start again from 001 as it would suggest that the class is full, and the next student must be added to another class.
Now, let’s assume that a student drops from a class after its data values are inserted by the nextval function, we will have to alter the sequence to assign the roll numbers in a hierarchical order by the Setval function.
In this piece of code, we have set the next value of the nextval function to “012” by stating it as false. So, the student’s data that we will enter after the “012” roll number student, who dropped out of class we automatically get the “012” roll number as you can see in the demonstration below:
You can verify the output from the appended image.
As we executed the insert query for entering a value in the table, the nextval function gave the value that was set by the Setval function in the sequence sq_1.
Different Ways to Use Setval Function
If we have to ensure the last value of the nextval function is true and we can add the other value as per the constraints defined in the sequence, we can write this query in two different ways in the PostgreSQL environment:
- Setval function without the Boolean state.
- Setval function with the Boolean state.
Setval Function Without the Boolean State
The syntax for executing the Setval function without the Boolean state is stated below:
The output can be verified from the appended screenshot.
In the above syntax, the next value of the nextval function will be set to “053” which means the newest value of the function is utilized.
Setval Function with the Boolean State
The Setval function can also be executed with the Boolean function, the statement for that is:
The output can be verified from the appended screenshot.
In both the above examples the next value of the nextval function will be set to “053” as it satisfies the constraint that the latest value of the nextval function is utilized. In the output above, when we inserted the next value using an insert query with the nextval function in the sequence after 52 it gave 53 in the table as shown above.
Conclusion
In this article, we have learned about the concept of sequences. We discussed why we use these sequences in our tables to generate values in this article. The different sequence functions that are used to alter the sequences in the PostgreSQL were also discussed. One of them was the Setval function that we discussed in a great ordeal in this article. The different types of uses this function can be utilized were also discussed. In the end, we also looked at how this function works in the PostgreSQL environment with its different kinds of constraints given in the parameter.
