1

I have a id field where id is serial and in the same table i have unique constraints. The problem here is when an unique constraint fails if the current ID value is 5 then after failing the next ID value gets to 7. Actually my need is to get 6 for the next ID value. Can any one help ?

Jithin Kumar S
  • 701
  • 2
  • 9
  • 20
  • That's not a problem. Sequences are bound to have gaps and there is nothing to worry about. The only job of a generated ID value is to be unique. It doesn't matter if that value is `42`, `39785`, `-7463` or `6` –  Nov 05 '19 at 06:19
  • Acutaly the problem is based on this ID(serial) i have a trigger which set another unique name(lets say concat('abc', id::text). But since the gap is coming in between(like 52, next if failed then 54) then unique name also get affected by this gap. ID making gap i dont have problem but the other field which is using this id needs to be sequential for me. – Jithin Kumar S Nov 05 '19 at 06:27
  • Then you have inappropriately designed this "unique name". The sequence designed in such a manner as to guarantee gaps. If you cannot have gaps in the other field you can create a parameter table that is updated and locked until committed. But that's not good in a multi user environment. – Belayer Nov 06 '19 at 01:52
  • @Belayer : True, mistake from my part, changing the DB column. my ultimate aim is to retrieve like 'C001', 'C002' ,etc. For that my plan is like will create a column called prefix column and another column called code column which expects values like 1, 2, 3 ,4 etc. and at retrieval i would concat with lpad and provide the result. Will this approach suffice ? – Jithin Kumar S Nov 06 '19 at 10:42
  • Well this doesn't solve the non-gap sequence issue, but if you deriving the column as access time then think about defining it as a virtual column. Good luck on the non-gap sequence. – Belayer Nov 06 '19 at 15:32

0 Answers0