postgres autoincrement not updated on explicit id inserts

Question

I have the following table in postgres:

CREATE TABLE "test" (
    "id" serial NOT NULL PRIMARY KEY,
    "value" text
)

I am doing following insertions:

insert into test (id, value) values (1, 'alpha')
insert into test (id, value) values (2, 'beta')

insert into test (value) values ('gamma')

In the first 2 inserts I am explicitly mentioning the id. However the table's auto increment pointer is not updated in this case. Hence in the 3rd insert I get the error:

ERROR:  duplicate key value violates unique constraint "test_pkey"
DETAIL:  Key (id)=(1) already exists.

I never faced this problem in Mysql in both MyISAM and INNODB engines. Explicit or not, mysql always update autoincrement pointer based on the max row id.

What is the workaround for this problem in postgres? I need it because I want a tighter control for some ids in my table.

UPDATE: I need it because for some values I need to have a fixed id. For other new entries I dont mind creating new ones.

I think it may be possible by manually incrementing the nextval pointer to max(id) + 1 whenever I am explicitly inserting the ids. But I am not sure how to do that.

Junco

Answer 1

That's how it's supposed to work - next_val('test_id_seq') is only called when the system needs a value for this column and you have not provided one. If you provide value no such call is performed and consequently the sequence is not "updated".

You could work around this by manually setting the value of the sequence after your last insert with explicitly provided values:

SELECT setval('test_id_seq', (SELECT MAX(id) from "test"));

The name of the sequence is autogenerated and is always tablename_columnname_seq.

Zendah

Strange behaviour. I am coming from MySQL as well. Thank you! – Intromission

I ran into the same problem and between you and ruby-forum.com/topic/64428#72333 (you basically say the same thing) you've resolved my issue. Thanks. – Imperil

I did all this, both the IDs in the table and the seq last stands at 3, but when I post new data, the integrity error raises saying id with 2 violates unique constraint? Did I break my db? – Verisimilitude

Answer 2

In the recent version of Django, this topic is discussed in the documentation:

Django uses PostgreSQL’s SERIAL data type to store auto-incrementing primary keys. A SERIAL column is populated with values from a sequence that keeps track of the next available value. Manually assigning a value to an auto-incrementing field doesn’t update the field’s sequence, which might later cause a conflict.

Ref: https://docs.djangoproject.com/en/dev/ref/databases/#manually-specified-autoincrement-pk

There is also management command manage.py sqlsequencereset app_label ... that is able to generate SQL statements for resetting sequences for the given app name(s)

Ref: https://docs.djangoproject.com/en/dev/ref/django-admin/#django-admin-sqlsequencereset

For example these SQL statements were generated by manage.py sqlsequencereset my_app_in_my_project:

BEGIN;
SELECT setval(pg_get_serial_sequence('"my_project_aaa"','id'), coalesce(max("id"), 1), max("id") IS NOT null) FROM "my_project_aaa";
SELECT setval(pg_get_serial_sequence('"my_project_bbb"','id'), coalesce(max("id"), 1), max("id") IS NOT null) FROM "my_project_bbb";
SELECT setval(pg_get_serial_sequence('"my_project_ccc"','id'), coalesce(max("id"), 1), max("id") IS NOT null) FROM "my_project_ccc";
COMMIT;

Cur

Answer 3

It can be done automatically using a trigger. This way you are sure that the largest value is always used as the next default value.

CREATE OR REPLACE FUNCTION set_serial_id_seq()
RETURNS trigger AS
$BODY$
  BEGIN
   EXECUTE (FORMAT('SELECT setval(''%s_%s_seq'', (SELECT MAX(%s) from %s));',
   TG_TABLE_NAME,
   TG_ARGV[0],
   TG_ARGV[0],
   TG_TABLE_NAME));
    RETURN OLD;
   END;
$BODY$
LANGUAGE plpgsql;  

CREATE TRIGGER set_mytable_id_seq
AFTER INSERT OR UPDATE OR DELETE
ON mytable
FOR EACH STATEMENT
  EXECUTE PROCEDURE  set_serial_id_seq('mytable_id');

The function can be reused for multiple tables. Change "mytable" to the table of interest.

For more info regarding triggers:

https://www.postgresql.org/docs/9.1/plpgsql-trigger.html

https://www.postgresql.org/docs/9.1/sql-createtrigger.html

Dubuffet

Answer 4

Slightly deviating from the original question, but it is now recommended to use IDENTITY columns over SERIAL. Apart from some other advantages, The default behavior there is to block manual inserts. Which at least prevents problems in case of accidental manual inserts. However, OP's question requires specific numbers inserted, so not a real solution here.

https://www.postgresql.org/docs/15/sql-createtable.html

PostgreSQL: serial vs identity

https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_serial

Dubuffet