0

i am currently using ormar in python for postgresql stuff and i am learning as well. by using this docker git with fastapi, postgress and traefik as template to start my project, i found out the example is using ormar inside and so i try to use it as well by converting my codes into it.

for this case, i would like to truncate the table first before adding another new set of data, with this, my ID as primary key for each row will be reset instead of incrementing everytime i delete and reinsert the data.

for now i am using this command for delete

await MyClass.objects.delete(each=True)

with that code above, i able to delete each row and re insert the data, but my ID is increasing instead of resetting. What can i do to make my ID reset each time i re insert the data into the table?

user1897151
  • 493
  • 2
  • 9
  • 28

1 Answers1

2

Postgresql numeric ids are generated by sequence objects. Truncating a table or deleting all rows does not reset the sequence.

You can reset a sequence like this:

SELECT setval('tbl_seq_id'::regclass, 1, false)

where tbl_seq_id is the sequence associated with your id column and 1 is the value you want for the next id.

You can obtain the name of the sequence associated with a column by calling the pg_get_serial_sequence function (credit to Evan Carroll and this answer).

test# select pg_get_serial_sequence('tbl', 'id');
 pg_get_serial_sequence 
════════════════════════
 public.tbl_id_seq
snakecharmerb
  • 47,570
  • 11
  • 100
  • 153