Postgres: How to insert row with autoincrement id

Posted on

Question :

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

Answer :

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');

INSERT INTO public.tablename (id, operator, text) values((SELECT MAX(id)+1 FROM public.tablename), 'OPERATOR',''); 

Leave a Reply

Your email address will not be published. Required fields are marked *