22

There is a Table "context". There is an autoincrement id "context_id". I am using sequence to retrieve the next value.

SELECT nextval('context_context_id_seq')

The result is: 1, 2, 3,...20....

But there are 24780 rows in the "context" table

How can I get the next value (24781)?

I need to use it in the INSERT statement

3 Answers 3

36

Apparently you inserted rows into that table without using the sequence and that's why they are out of sync.

You need to set the correct value for the sequence using setval()

select setval('context_context_id_seq', (select max(context_id) from context));

Then the next call to nextval() should return the correct value.

If the column is indeed defined as serial (there is no "auto increment" in Postgres) then you should let Postgres do it's job and never mention it during insers:

insert into context (some_column, some_other_column)
values (42, 'foobar');

will make sure the default value for the context_id column is applied. Alternatively you could use:

insert into context (context_id, some_column, some_other_column)
values (default, 42, 'foobar');
3
  • 2
    and when using serial count still can jump over 1 or 2 id's. That because postgresql first takes nextval then tries to insert and if it fails for somereason that id is already used May 22, 2014 at 9:43
  • 5
    @user503207: a sequence is never guaranteed to be gap-free.
    – user1822
    May 22, 2014 at 9:43
  • 2
    Autoincrement fields, sequences, serials, identity whatever you want to call them, generally have a caching mechanism which means that strictly linear gap-free monotonically increasing sequences of integers are impossible to guarantee.
    – Vérace
    May 22, 2014 at 11:50
0

In my case that sql worked: ALTER SEQUENCE "SharedLogs_id_seq" RESTART WITH 111;, see https://stackoverflow.com/questions/8745051/postgres-manually-alter-sequence/8750984#8750984

1
  • Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.
    – Community Bot
    Apr 10 at 22:03
-1
INSERT INTO public.tablename (id, operator, text) values((SELECT MAX(id)+1 FROM public.tablename), 'OPERATOR',''); 
2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.