Delete all records from entity and reset id counter

thecosmo

Hello,
During application testing I created test records in entities for which REI3 automatically created IDs in ascending order.
Now I need to delete all test records and reset the ID counter (so that IDs start from 1 again).
Can you tell me how to do this?

gabriel

It´s possible via SQL but in general there should not be a need to do this other than OCD 😉

Why do you need to reset IDs?

thecosmo

gabriel

Because I need to import a dataset into REI3 via CSV and the order of import is important - please don't specify why 🙂

I tried to reset via "truncate" and "drop", but when I entering new test records, REI3 count records from old ID values, for example: in fact there were 70 test records, I deleted them, but the numbering still continued from 71 and so on.

gabriel

You need to restart the ID counting sequence (Postgres lingo). The syntax is:
ALTER SEQUENCE [schema].[sequence_name] RESTART WITH 1;

r3 uses sq_ + the ID of the relation as sequence name. An example where the app is called app1 and the relation has the ID 115245f4-3ecf-4174-b98c-a430e3f86a71:
ALTER SEQUENCE app1."sq_115245f4-3ecf-4174-b98c-a430e3f86a71" RESTART WITH 1;

This will reset the ID counter to 1. If you still have data in the relation, this can result in duplicate primary key errors.

To execute the SQL, you can either connect to the database system, or run it in a backend function directly in r3.

thecosmo

gabriel
Thanks for the tip. I'll try to delete the data from the tables and reset the ID numbering