0

I was doing a simple crud on nestjs and using postgres, then i noticed something strange.

I made an entity with a unique string column

@Entity()
export class Model {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({unique:true})
  name: string;
}

but then everytime i try to add a row, and if this row dont get added because the unique restriction it increases the current id of the database anyways, and then the next line jumps some ids.

exemplifying: if i try to add a Model with name 'model 1', it will be id 1, since it's the first, but then, if i try to add 'model 1' 15 times, and it wont be added because i made the restriction, the next time i insert a row, like 'model 2' it will jump a lot of ids, and wont be 2, it will be 16. Is it how it should work? it looks like I am wasting ids for no reason.

2 Answers 2

2

It's not a bug; it's a feature.

This is a known behavior of sequence types across multiple platforms. Don't try to solve for it. You're not going to run out IDs.

By the time you do encounter any of the possible problematic tradeoffs this behavior causes, you will have learned enough about specific platform you're on to have plenty of options.

0

Autoincrement generator generates any definite value only once. If some value was generated already then it will be generated again never (until the generator reset, of course).

When you try to insert a row then the server performs the next steps (simplified):

  1. Ask a value from the generator (and default values from the table metadata if needed) and build complete line to be inserted.
  2. Check constraints and another dependencies.
  3. Insert row if no violation was detected on the previous steps.

I.e. when the constraint violation is detected the autoincrement value is already generated. And (see above) it won't be generated again. A gap occures.

This is a norma and shouldn't be fixed. If you need in consecutive enumeration then generate it in the query. Or create separate column and fill it with the values programmatically in the trigger.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.