Postgres pull multiple future keys from sequence

In Oracle I can do something like this to fetch a bunch of keys.

select seq.nextval from dual connect by level <= 1000;

I Postgres there is a nextval 开发者_如何转开发function, but how can multiple values be pulled at once?`

If someone also knows the answer for InnoDb I would be happy to read it.

---

In postgresql, you can use something like this:

select nextval('mysequence') from generate_series(1,1000);

to get 1000 values from the sequence! I don't think it's particularly safe to assume that these will be consecutive values, though. You might need to wrap the select in a lock statement to ensure that competing results don't interleave. If you don't care about consecutive values, then you don't need a lock.

---

PostgreSQL has a suite of functions that manipulate sequences. So you can do something like this. (But don't. See "use PostgreSQL's native cache" below.)

drop sequence test;
create sequence test;

-- You'll want to wrap these in a single transaction.
select nextval('test');                               -- Returns 1
select (setval('test', currval('test') + 1000)) as t; -- Returns 1001
select nextval('test');                               -- Returns 1002

There are a couple of other ways to get a bunch of values from a sequence. See docs for CREATE SEQUENCE.

Have the sequence increment by 1000 instead of by 1. Let your application handle doling out the thousand values lower than nextval(). You will probably end up throwing away a lot of numbers.

drop sequence test;
create sequence test increment by 1000;
select nextval('test'); -- Returns 1; do this when you create the sequence.
select nextval('test'); -- Returns 1001

Use PostgreSQL's native cache. I'm pretty sure this will be the most reliable and robust approach.

drop sequence test;
create sequence test cache 1000;
select nextval('test'); -- Returns 1.
select nextval('test'); -- Returns 2 from the cache; doesn't touch the sequence.

A different, concurrent session won't see the same thing.

select nextval('test'); -- Returns 1001