3
select nextval ('mySchema.mySequence')

This command will return the next value of a sequence.

How can I get the next N values from a sequence in SQL?

My current setup uses postgreSQL, and Hibernate's native SQL queries, but any solution is appreciated.

rogerdpack
  • 62,887
  • 36
  • 269
  • 388
iliaden
  • 3,791
  • 8
  • 38
  • 50
  • 1
    What database product and version? – Thomas Jun 17 '11 at 17:07
  • postgreSQL. Queries made from Hibernate, where I enter native SQL code. Ideally, this should be database-independent. – iliaden Jun 17 '11 at 17:09
  • 3
    @iliaden - First, you should tag your question with Hibernate. Second, true database-independence is nearly impossible to achieve. The implementation (or lack of) of sequences for example vary widely among database products. – Thomas Jun 17 '11 at 17:11
  • thanks - but any solution for this problem would be appreciated. – iliaden Jun 17 '11 at 17:13
  • 2
    See answers from [Returning multiple SERIAL values from Postgres](http://stackoverflow.com/questions/5875953/returning-multiple-serial-values-from-posgtres-batch-insert) – j.w.r Jun 17 '11 at 17:22
  • You could write a stored procedure nextvals(n) that accepts the count of sequential numbers desired and increments the store by that number. That would guarantee the numbers are contiguous. How do you want those numbers to be returned? as 100 rows containing a single column? As a single row with two columns, the first and last number in the sequence? What purpose do you have in mind for it? – Tim Jun 17 '11 at 17:26
  • 1
    @j.w.r - you posted the link to the working solution... Is there a way to mark a comment as the right solution? `SELECT nextval( 'mySchema.mySequence' ) FROM generate_series( 1, 100 ) n` – iliaden Jun 17 '11 at 17:34

3 Answers3

3

You can define your sequence with INCREMENT BY 100 and then, when you get your next value N using SQL just use values N + 1, N + 1, ..., N + 99 in your Java code.

Olaf
  • 6,249
  • 1
  • 19
  • 37
  • I never know how many values I would need at once. 100 was for example. so this solution doesn't really apply. – iliaden Jun 17 '11 at 17:26
  • @iliaden: Well, you should have some upper limit. If you are working with large amounts of data in Hibernate, you should be splitting it in batches with, say, 5000 records/objects limit. In that case INCREMENT BY 5000 would work just fine. – Olaf Jun 17 '11 at 17:30
3

Here is a link to an answer provided on a related question.
How to select multiple rows filled with constants?

The solution for Postgres, Oracle, and SQL Server are all different.

The Postgres solution is:

SELECT  l
FROM    generate_series(1, $n) l

In your case instead of select l you would select nextval('mySchema.mySequence')

Community
  • 1
  • 1
dave
  • 1,520
  • 11
  • 8
  • that's an interesting approach, but I can't quite figure out how to make it 1) read from a sequence 2) set the sequence value to the last generated value. – iliaden Jun 17 '11 at 17:27
  • http://stackoverflow.com/questions/8292199/query-several-nextval-from-sequence-in-one-satement might be how to do it in oracle... – rogerdpack Jun 25 '13 at 22:37
2

Please use this SQL query. Note: it will work only in case of Oracle.

select your_sequence_name.nextval from dual connect by level <= 100
Asheesh Gupta
  • 21
  • 1