Get the next 10 values of the SEQUENCE at once

Question

I want to get the next 10 values of the SEQUENCE at once.

I can get those individually by calling

SELECT setval('myseq', ...);           -- Next nextval will return 43

10 times back to back.

On Microsoft SQL server, I can do something like below to get all the 10 at once.

DECLARE
    @range_first_value  SQL_VARIANT
  , @range_last_value   SQL_VARIANT
  , @range_cycle_count  INT
  , @sequence_increment SQL_VARIANT
  , @sequence_min_value SQL_VARIANT
  , @sequence_max_value SQL_VARIANT;
EXEC sys.sp_sequence_get_range
    @sequence_name = N''                                -- nvarchar(776)
  , @range_size = 0                                     -- bigint
  , @range_first_value = @range_first_value OUTPUT      -- sql_variant
  , @range_last_value = @range_last_value OUTPUT        -- sql_variant
  , @range_cycle_count = @range_cycle_count OUTPUT      -- int
  , @sequence_increment = @sequence_increment OUTPUT    -- sql_variant
  , @sequence_min_value = @sequence_min_value OUTPUT    -- sql_variant
  , @sequence_max_value = @sequence_max_value OUTPUT    -- sql_variant

Is there an equivalent on PostgreSQL?

Answer 1

Use generate_series().

select nextval('myseq')
from generate_series(1, 10);

Db<>fiddle.

Answer 2

The most efficient way to get the next ten sequence values is to call nextval() ten times, like klin's answer suggests.

For that to be efficient, you should define the sequence with a CACHE value of 10 or more, like

CREATE SEQUENCE myseq CACHE 15;

With a definition like that, the first nextval() call will fetch 15 value from the sequence, return the first one and cache the rest in the database session. The next 14 nextval() calls will use these cached values and don't actually have to access the sequence.

Answer 3

You can return an array of int/bigint as necessary:

select array_agg( nextval('myseq') )
  from generate_series(1, 10);