Entity ID / Recorder ID cross reference

1

I created an MQTT entity in configuration and failed to give it a unique ID. Then I changed it’s name once or twice, and lost history. I’d like to review my backups in order to find the unique recorder PK(s) corresponding to this entity, in order to try to fix up the history in the DB. How can I do that?

2

OK, I’ve gotten a bit further. I found the old and the new entities in the states_meta table. And my basic idea would be to update the metadata_id FK in various tables so all the records with the old metadata_id would change to have the new one.

3

This seems to have done the trick, in case anyone finds themselves in a similar situation.

UPDATE states
SET metadata_id = 529
WHERE metadata_id = 1900;

UPDATE "statistics"
SET metadata_id = 529
WHERE metadata_id = 1900;

UPDATE statistics_short_term
SET metadata_id = 529
WHERE metadata_id = 1900;