Tag: sequences

How CURRVAL() Works in PostgreSQL

Posted on by Ian

In PostgreSQL, the currval() function returns the value most recently returned by nextval() for the specified sequence in the current session.

The currval() function is very similar to the lastval() function, except that lastval() doesn’t require the name of a sequence like currval() does. That’s because lastval() doesn’t report on any particular sequence – it reports on the last time nextval() was used in the current session, regardless of which sequence was used. The currval() on the other hand, only reports on the specified sequence.

Continue reading →

How LASTVAL() Works in PostgreSQL

Posted on by Ian

In PostgreSQL, the lastval() function returns the value most recently returned by nextval() in the current session.

The lastval() function is very similar to the currval() function, except that lastval() doesn’t require the name of a sequence like currval() does. That’s because lastval() doesn’t report on any particular sequence – it reports on the last time nextval() was used in the current session, regardless of which sequence was used.

Continue reading →

How to Change the Range of a Sequence in SQL Server

Posted on by Ian

SQL Server sequence objects allow us to increment or decrement through a range of numbers. Once set, we don’t need to change anything. We can simply use NEXT VALUE FOR to generated the next sequential number.

But what if we want to change the sequence to use a different range?

For example, we created a sequence that increments between 0 and 100, but now we want to change it to increment between 200 and 300.

Easy. We can use the ALTER SEQUENCE statement to do just that.

Continue reading →

Fix “START value (…) cannot be greater than MAXVALUE (…)” When Creating a Sequence in PostgreSQL

Posted on by Ian

If you’re getting an error that reads something like “START value (11) cannot be greater than MAXVALUE (10)” in PostgreSQL when you’re trying to create a sequence, it’s because your sequence’s start value is higher than its maximum value, when it should be lower or the same.

To fix this issue, be sure that the sequence’s maximum value is not less than its start value.

Continue reading →

How to Specify the Data Type when Creating a Sequence in SQL Server

Posted on by Ian

When creating a sequence object in SQL Server, the default data type is bigint. However, we can change this so that the sequence uses a data type of our choosing, as long as it’s an integer type (see below for accepted data types).

We can set the data type of a sequence by using the AS argument when defining the sequence.

Continue reading →

How to Create a Repeating Sequence in SQL Server

Posted on by Ian

When we create a sequence object in SQL Server, we have the option of making it a repeating sequence or a nonrepeating sequence. By repeating I mean, we can have the sequence continually start again once the min/max value has been reached. In other words, we can have the sequence reiterate over and over again.

We can do this with the CYCLE argument.

Continue reading →

Fix “NEXT VALUE FOR function does not support the PARTITION BY clause.” (Error Msg 11716) in SQL Server

Posted on by Ian

If you’re getting an error that reads “NEXT VALUE FOR function does not support the PARTITION BY clause” in SQL Server, it’s probably because you’re trying to use the PARTITION BY sub clause in an OVER clause when using NEXT VALUE FOR to increment a sequence object.

In other words, the NEXT VALUE FOR function does not support the PARTITION BY sub clause of the OVER clause.

To fix this issue, either remove the PARTITION BY clause or change the statement to use another method for partitioning the results.

Continue reading →