How do you reset a serial type back to 0 after deleting all rows in a table?



  • I previously asked https://dba.stackexchange.com/questions/294727/how-to-auto-increment-a-serial-column-on-update , and somebody posted a very helpful answer on db fiddle https://dbfiddle.uk/?rdbms=postgres_10&fiddle=871d2b1be9627095a15f07841dcb90b6 . This has worked really well for me. However, now I would like to ask a new (related) question :

    Suppose I wipe out all the rows in the table (i.e. in the db fiddle example the apple and orange rows are deleted by doing "delete from fruits where id > 0"). If I insert another fruit like watermelon, the id will start at 4, not 1. How do I force the id type to reset back to 0, so that the next insert will have id=1 ?



  • Simple (assuming you know the sequence name):

    SELECT setval('fruits_id_seq', 1, false);
    

    Or:

    ALTER SEQUENCE payments_id_seq RESTART WITH 0;
    

    Safe (you don't know the sequence name or are not certain whether the table is empty):

    SELECT setval(pg_get_serial_sequence('fruits', 'id')
                , COALESCE(max(id) + 1, 1)
                , false)
    FROM   fruits;
    

    db<>fiddle https://dbfiddle.uk/?rdbms=postgres_10&fiddle=402899ba92090d12b8d57279abed3c59

    See:

    • https://stackoverflow.com/a/23390399/939860
    • https://stackoverflow.com/a/8750984/939860
    • https://dba.stackexchange.com/questions/193568/how-to-fix-all-duplicate-key-value-violates-unique-constraint/193577#193577



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2