Update sequence on row INSERT [duplicate]

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. — user330315
– user330315, Commented Apr 30, 2014 at 8: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
– Bart Friederichs, Commented Apr 30, 2014 at 9: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) — user330315
– user330315, Commented Apr 30, 2014 at 9:21
If that is the case, exports (especially backups) should contain the setval() calls too. pg_dump does that, f.ex. — pozs
– pozs, Commented Apr 30, 2014 at 9: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
– Bart Friederichs, Commented Apr 30, 2014 at 9:48

pozs · Accepted Answer · 2014-04-30 08:58:31Z

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.

Collectives™ on Stack Overflow

Update sequence on row INSERT [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related