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)

score 68 · Accepted Answer · edited May 13 '14 at 20:17

68

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.

edited May 13 '14 at 20:17

rogerdpack

62,887
36
269
388

answered May 22 '09 at 05:09

6

Note that the 3 numbres (nextvals) are not guaranteed to be sequential. – cquezel Nov 24 '15 at 18:56

score 15 · Answer 2 · answered May 25 '09 at 20:33

15

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.

answered May 25 '09 at 20:33

Ants Aasma

53,288
15
90
97

Interestingly, that article appears to be written by the person who wrote the answer I accepted :-) Regardless, the generate_series fits my need because the number of ids I need is around a dozen. On a very rare case, maybe 100... so the downfalls of the other solution presented in the article are too steep for my uses. +1 for nice link though! – Mike Stone May 28 '09 at 02:26

TML · Answer 3 · 2009-05-22T04:21:44.690


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;

Note that due to the use of 'setof', you have to call this as a table: `select * from foo(500);` — TML, May 22 '09 at 04:22

score 1 · Answer 4 · answered May 23 '09 at 21:16

1

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.

answered May 23 '09 at 21:16

NA.

6,451
9
36
36

Appreciated, but the downfalls pointed out in the link from Ants Aasma are too steep for me to alter the increment... and the number of ids I need varies, so I prefer obtaining all the ids individually (which makes the code that uses the ids simpler anyways). – Mike Stone May 28 '09 at 02:29

score 0 · Answer 5 · answered May 22 '09 at 03:49

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.

Select multiple ids from a PostgreSQL sequence

5 Answers5

Linked