Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Select multiple ids from a PostgreSQL sequence

Is there a concise way to select the nextval for a PostgreSQL sequence multiple times in 1 query? This would be the only value being returned.

For example, I would like to do something really short and sweet like:

SELECT NEXTVAL('mytable_seq', 3) AS id;

And get:

 id  
-----
 118
 119
 120
(3 rows)
like image 733
Mike Stone Avatar asked Sep 01 '25 01:09

Mike Stone


2 Answers

select nextval('mytable_seq') from generate_series(1,3);

generate_series is a function which returns many rows with sequential numbers, configured by it's arguments.

In above example, we don't care about the value in each row, we just use generate_series as row generator. And for each row we can call nextval. In this case it returns 3 numbers (nextvals).

You can wrap this into function, but I'm not sure if it's really sensible given how short the query is.

There is a great article about this exact problem: "getting multiple values from sequences".

If performance is not an issue, for instance when using the sequence values dwarfs the time used to get them or n is small, then the SELECT nextval('seq') FROM generate_series(1,n) approach is the simplest and most appropriate.

But when preparing data for bulk loads the last approach from the article of incrementing the sequence by n from within a lock is appropriate.

like image 25
Ants Aasma Avatar answered Sep 02 '25 15:09

Ants Aasma