I want to show you quickly how you can create your own sequence that is not tied to a serial column and some of the sequence manipulation functions that you could use should you end up needing them. It is very simple, create sequence, and then you give it a sequence name and a data type. Then you do have several more options, you can set the increment, by default it is 1, you could change it to 10, you could change it to 12, you can change it to whatever you want. You can change the increment. You can also set the start and min values.

Setting the start value can be very helpful if you are importing data from another system that already has some sort of sequence or pseudo sequence. You can look and see what the max number is there and then add 1 to it and say, that's my start value for this sequence. We're going to leave it at 1. The max value you can also set and you can set that to the max of BIGINT if you want, which is what we've done here. You could also set it to 100. You can just simply say, once you reach 100, you run out of sequence numbers. I don't know why you would do that, but I don't know what your application is. That's totally viable as well.

Finally, you do have a cash value here, and this is telling Postgres how many of the sequence values to cash before it has to go back and pull new ones. The default is 1, I usually just leave it at that. In fact, you can just leave that off. If we create a new sequence, then we have a few things we can do. We can do select nextval('seq'); and if you see that, it's just going to keep incrementing. This pulls a value off and increments the sequence. It would be interesting, I think, if we had two different sessions going here. I'm going to pull both of these up here, and if we ran select nextval('seq'); over here as well, we would see 24, 25, 26, 27. What is interesting is there's another function called currval('seq'); and this gives you the last value that next value gave you.

Let's watch this. If we run select nextval('seq'); we get, I'm just going to mark it down, we get 28 over here. If we run currval('seq'); we're going to continue to get 28. We're going to constantly get 28 over here. That seems fine because that is the value we got back, and that's also the pointer inside the sequence to show you what the last value was. Now over here, if we run nextval a whole bunch, we're going to get that sequence up to 44. If we run currval over here again, it still retains the last value for which we ran nextval in this session. What does that even mean? That means that inside a single session or inside a transaction, you could say, all right, I'm going to get a nextval.

I'm going to pull a value off of the sequence, but then I want to use it over and over and over again without assigning it to some variable somewhere, marking it down, doing whatever. You can just use the currval over and over and over. This remains at 28. What this is not, importantly, what this is not is this is not the current value of the sequence globally. This is not where does the pointer rest in the sequence itself, this is, show me the last time I ran nextval.

In fact, if you haven't run nextval at all, running currval will give you an error because there is no current value because you've never run next value. You can also, we don't need two anymore. You can also run setval. If you did setval with the sequence name, you can reset it to whatever you want. You can run setval 1, and if we ran nextval, we are reset. Be careful doing that because you know your domain and if you reset a sequence, is that going to create duplicate entry errors, potentially, that might be exactly what you're looking for, but if you reset it down to the bottom, it may be trouble.

You can of course reset it to a much higher number and you're in pretty good shape there. It's kind of fun to know that exists. It could potentially be extremely useful, but either way, knowledge for the sake of knowledge is edifying but this is a useful thing that Postgres offers and hopefully you can find a place to use this in your application or your business.