IDs are not sequential anymore

💬 Queries and Resources
, ,
1

Hello, I have some issues with one of my tables, for some reason the IDs are not auto sequential anymore and when I try to make the default expression "tablename_id_seq" it does not work. My other tables have the same default expression and they work like they should, I have attached two pictures of two different ID columns and to me they look exactly the same. What could cause this?

image
image443×602 20.2 KB

image
image444×565 17.5 KB

Thank you,

Isac

2

Welcome to the community, @Isac_Karvia! Thanks for reaching out and for your patience. :slightly_smiling_face:

To me, this indicates that the sequence may no longer exist. It's hard to say what may have caused this, but we can verify that this is the root cause by executing the following query:

SELECT * FROM pg_sequences;

If you don't see the relevant sequence in the resulting list, you can recreate it with:

CREATE SEQUENCE users_id_seq
    START WITH 42
    INCREMENT BY 1;

I hope that helps! Let me know if you run into any additional issues or have any questions.

2 Likes
3

Hey @Isac_Karvia,

I understand your issue you can use this query for your problem according to your table.

CREATE SEQUENCE IF NOT EXISTS items_id_seq;
ALTER TABLE items ALTER COLUMN id SET DEFAULT nextval('items_id_seq'), ALTER COLUMN id SET NOT NULL;
ALTER TABLE items ADD CONSTRAINT items_pkey PRIMARY KEY (id);
SELECT setval('items_id_seq', (SELECT GREATEST(COALESCE(MAX(id),0) + 1, nextval('items_id_seq')) FROM items));

Also check this forum
Primary Key

5 Likes