Select multiple ids from a PostgreSQL sequence

Question

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)

Answer 1

My current best solution is:

SELECT NEXTVAL('mytable_seq') AS id
UNION ALL
SELECT NEXTVAL('mytable_seq') AS id
UNION ALL
SELECT NEXTVAL('mytable_seq') AS id;

Which will correctly return 3 rows... but I would like something that is minimal SQL for even as much as 100 or more NEXTVAL invocations.

Answer 2

CREATE OR REPLACE FUNCTION foo() RETURNS SETOF INT AS $$
DECLARE
    seqval int; x int;
BEGIN
x := 0;

WHILE x < 100 LOOP
    SELECT into seqval nextval('f_id_seq');
    RETURN NEXT seqval;
    x := x+1;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql STRICT;

Of course, if all you're trying to do is advance the sequence, there's setval().

You could also have the function take a parameter for how many times to loop:

CREATE OR REPLACE FUNCTION foo(loopcnt int) RETURNS SETOF INT AS $$
DECLARE
    seqval int;       
    x int;
BEGIN
x := 0;
WHILE x < loopcnt LOOP
    SELECT into seqval nextval('f_id_seq');
    RETURN NEXT seqval;x := x+1;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql STRICT;

Answer 3

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.

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

Answer 4

Unless you really want three rows returned I would set the sequence to 'INCREMENT BY 3' for each select. Then you can simple add 1 and 2 to the result have have your three sequence numbers.

I tried to add a link to the postgresql docs, but apparenty I am not allowed to post links.

Answer 5

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.