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

thecosmo

hi @gabriel
Following your advice, I deleted all records from the entity and reset the entity ID counter to 1.

Then I imported 3000 records into the entity via the CSV - I did this by assigning my own values (1-3000) to the corresponding ID field.

I encountered a problem that after importing all the records, I cannot create new records due to a violation of the uniqueness of the ID, as error R3 tells me.

I assume that the ID counter in the entity does not process the ID values that came from the CSV import, and is not updated to the maximum ID value after import.

How can I solve this problem?

gabriel

It makes sense that you get violations when you feed your own IDs during CSV import.

For an auto-incrementing primary key to work correctly, it has to generate IDs. So when you reset the counter to 0, then import 3000 records with your IDs, the counter is still at 0. So the next ID it would generate would be 1.

In general:

There is no need to reset ID values unless you approach an overflow.
IDs are not meant for people to identify records on the frontend so there is no need to "clean them up".
Overwriting auto-incrementing primary keys is very seldom a good idea - especially if you need to import multiple entities. You should not rely on these IDs, especially when resolving foreign keys.
If you need user-visible, unique values, generated keys are usually a good idea and more human-readable (something like 'REQ_25_0033').
If you need to sync data between systems, use UUIDs or other unique identifiers (names, generated keys, etc.).
There are few reasons to supply your own IDs in imports, unless you are doing updates on existing records. When you import primary key IDs, you will get into conflict with the internal counter - so you should avoid it if at all possible.