Question 225820 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Postgres sequence getting messed up / overwriting items

Table:

CREATE SEQUENCE vars_id_seq;
CREATE TABLE vars (
    id INT PRIMARY KEY NOT NULL DEFAULT nextval('vars_id_seq'),
    name VARCHAR(30) NOT NULL,
    value VARCHAR(30)
);
ALTER SEQUENCE vars_id_seq OWNED BY vars.id;

I inserted some rows, and all was well. Today, however, when inserting rows, I found that it did one of two things (and both happend at different times):

  • It started overwriting existing items when I ran INSERT commands (without specifying an ID, i.e. it should have created a new item)

  • It error’d that there was a duplicate key, because it was trying to resume the sequence from 7, for some reason, even though there was an item with ID 7, and items with IDs above 7.

So my question is, conceptually, how could it be that my sequence’s current value is lower than the highest ID in my table? Sure, I’ve deleted rows from it over time, but I would have expected the sequence to keep in sync.

(I know a later way of doing auto-inc in PG is via SERIAL / BIGSERIAL column types, but for some reason that’s not showing as an available type in my DB. I’m from a MySQL background, incidentally.)

Thank you.

====== EDIT

This is what my insert query looks like:

    INSERT INTO ${table}s (${Object.keys(params).map(col => `"${col}"`).join()})
    VALUES (${Array(Object.keys(params).length).fill().map((val, i) => '$'+(i+1)).join()})
    ON CONFLICT (id)
    DO UPDATE SET ${Object.entries(params).map((pair, i) => `"${pair[0]}" = $${i+1}`).join()}
    RETURNING id
Tags:

2

Answers


  1. 0

    So my question is, conceptually, how could it be that my sequence’s current value is lower than the highest ID in my table? Sure, I’ve deleted rows from it over time, but I would have expected the sequence to keep in sync.

    Sequences aren’t going to magically update when you delete rows. If you delete a row in the table with a sequence those sequence numbers are gone. If you’ve inserted data specifying your own sequence number then you could get out of sync.

    Login or Signup to reply.
  2. 0

    Let the database handle all this stuff for you and make it "impossible" to manually handle the id’s by using a generated column:

    CREATE TABLE vars (
    id INT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    name VARCHAR(30) NOT NULL,
    value VARCHAR(30)
);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search