0

In my dataBase i have 120 tables , and each table get his own sequence like this :

CREATE TABLE myTable
(
  id bigint NOT NULL DEFAULT nextval('mySequence'::regclass),
...

Sometimes i am forced to add some data from postgres and it create a problem with the sequence : each time i add an information the sequence isn't updated

my question is : is there any sql request i can use to list the table with the sequence and compare the last_value FROM my sequence with the MAX(id) FROM myTable

Additionnal question : is there any risque to compare the last_value with MAX(id)

Yagami Light
  • 1,756
  • 4
  • 19
  • 39
  • "is there any risque to compare the last_value with MAX(id)" - yes, if you have multiple connections writing. – Mitch Wheat Mar 07 '18 at 08:06
  • @MitchWheat i am not firing multiple queries in the same time if this is what you mean by multiple connections writing the thing is sometimes i am forced to access (edit and delete) my data from postgres and it kind of diturbe my sequence – Yagami Light Mar 07 '18 at 08:09
  • 1
    Why not simply reset the sequence after the inserts? See e.g. here: https://stackoverflow.com/a/23390399/330315 –  Mar 07 '18 at 08:18
  • @a_horse_with_no_name i want to detect the error between the current id and the sequence but i will use your solution in the futur – Yagami Light Mar 08 '18 at 08:05
  • @YagamiLight: and what do you do if you detect the discrepancy? Most certainly sync the sequence, right? –  Mar 08 '18 at 08:08
  • @a_horse_with_no_name yes but i have to know witch table create the problem imagine i have to search 120 tables – Yagami Light Mar 08 '18 at 08:09

0 Answers0