Update sequence on row INSERT

Question

I have a table with a primary key set on an id field that uses a sequence to generate numbers as its default value. When inserting records, this works fine and the sequence is correctly updated. However, if I insert records with an id field (e.g. an import from a backup), the sequence is not updated.

CREATE TABLE public.my_table (
  my_id integer NOT NULL DEFAULT NEXTVAL('my_id_seq'),
  name text,
  CONSTRAINT my_primary PRIMARY KEY (myid)
);

CREATE SEQUENCE public.my_id_seq
   START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

This updates the sequence:

INSERT INTO my_table (name) VALUES ('Bart');

This doesn't:

INSERT INTO my_table (my_id, name) VALUES (12, 'Bart');

Causing problems when the sequence hits 12 and I try to insert without an ID.

Is there a good/standard way to do this automatically? I know MySQL does it in its AUTO_INCREMENT column (which is way I ran into this problem, I came from MySQL). I looked online and found some ways: using a trigger, or manually updating the sequence after inserting, but many of these resources are quite old. Perhaps version 9.1 has some features to address this?

The solution is quite simple: don't supply values for auto-generated columns. — , Apr 30 '14 at 08:55
@a_horse_with_no_name the problem only occurs when importing data into the tables, for example from backups. I have to supply those id's because they are foreign keys in other tables. — Bart Friederichs, Apr 30 '14 at 09:13
If that is part of an import, running a `setval()` after the import finished is probably the best way to do it: `select setval('my_id_seq', (select max(id) from my_table));` the trigger solution is an overkill in that situation I think (slows down the insert massively) — , Apr 30 '14 at 09:21
If that is the case, exports (especially backups) should contain the `setval()` calls too. `pg_dump` does that, f.ex. — pozs, Apr 30 '14 at 09:46
@pozs: thanks, but I export to Excel and use that same file also to import again. I'll have to create some sort of "finalize import" function that updates all the sequences. — Bart Friederichs, Apr 30 '14 at 09:48
That would do the job. My answer is more like a general answer, but only do that if you really *need* to; but this case i'm agree with @a_horse_with_no_name. Also consider locking that table for the time of the import. — pozs, Apr 30 '14 at 09:54
You can write a function that goes through all sequences and adjusts them to the current value of the related table. But that requires that you use a `serial` column or an `alter sequence owned by ..` in order to automate that (`owned by` will establish a "link" between the sequence and the column, so that you can find out which column is populated by which sequence using `pg_get_serial_sequence`) — , Apr 30 '14 at 10:15
Instead of just inserting raw data, you can write a function that will insert data using default `insert` without IDs and use `returning id` clause to get added record ID for further references. It is a bit complicated, but is concurrently safe. — Nick, Apr 30 '14 at 11:49

pozs · Answer 1 · 2014-04-30T08:58:31.487

That's a known limitation: sequences are incremented during the call of the nextval() function, which is your default value of your field. When you supply data at INSERT to that field, the default value's expression does not evaluates, that's why the sequence's value is not touched.

A workaround is to set up a trigger before/after INSERT to manually fix the sequence's value with setval(). But this way you should need to set up a trigger on UPDATE on that field too, to fix the sequence's value, when you just update some existing id to a higher id.

Another workaround is, that you write a stored function, which can produce an available value for that field & set your field's default value to that function's return value. Someting, like:

LOOP
    result = nextval('my_id_seq');
    EXIT WHEN NOT EXISTS (SELECT * FROM my_table WHERE my_id = result);
END LOOP;
RETURN result;

But be warned: the default functionality for sequences is safe for concurrent inserts (the current state of the sequence is global - transaction independent). If you supply explicit values to those fields, that won't be the case.

Update sequence on row INSERT

1 Answers1

Linked